MySQL: joining multiple columns into multiple tables?

Question

I have a set of three tables:

Dining_Tables
+ -------------------- + -------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ -------------------- + -------------- + ------ + ----- + --------- + ------- +
| dining_table | int (11) | NO | PRI | NULL | |
| bus_boy | varchar (35) | NO | | NULL | |
| waiter | varchar (35) | NO | | NULL | |
| server | varchar (35) | NO | | NULL | |
+ -------------------- + -------------- + ------ + ----- + --------- + ------- +

Poker_Tables
+ -------------------- + -------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ -------------------- + -------------- + ------ + ----- + --------- + ------- +
| poker_table | int (11) | NO | PRI | NULL | |
| dealer | varchar (35) | NO | | NULL | |
| pit_boss | varchar (35) | NO | | NULL | |
+ -------------------- + -------------- + ------ + ----- + --------- + ------- +

Computer_Tables
+ -------------------- + -------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ -------------------- + -------------- + ------ + ----- + --------- + ------- +
| computer_table | int (11) | NO | PRI | NULL | |
| programmer | varchar (35) | NO | | NULL | |
+ -------------------- + -------------- + ------ + ----- + --------- + ------- +

Each of these lines has a globally unique identifier of the table that is associated with it: ( dining_table, poker_table, computer_table) in the other column stores the name of the first and last person performing the roll.

In my model, one person can do several things. For example, Joe Smith could pretend to be sitting in computer_tableas a programmer, sitting in poker_tableas a dealer and waiting on dining_tableas a waiter.

My question is: I need a request that will allow me to get everything table_idsfor this person. In particular, the query will then return the list table_idsthat Joe Smith is currently on.

- :

select dining_table from Dining_Tables where 
      bus_boy = "Joe Smith" or
      waiter = "Joe Smith" or 
      server = "Joe Smith";

select poker_table from Poker_Tables where 
      dealer = "Joe Smith" or 
      pit_boss = "Joe Smith";

select computer_table from Computer_Tables where 
      programmer = "Joe Smith";

, , , . ?

Answer 1

, UNION, .

select dining_table as Table from Dining_Tables where 
      bus_boy = "Joe Smith" or
      waiter = "Joe Smith" or 
      server = "Joe Smith"
UNION
select poker_table as Table from Poker_Tables where 
      dealer = "Joe Smith" or 
      pit_boss = "Joe Smith"
UNION
select computer_table as Table from Computer_Tables where 
      programmer = "Joe Smith"

, .

"" , .

- :

, , , , Roles

TableTypes
ID    TypeName
-----------------------
1     Dining
2     Poker
3     Computer

Tables
ID    TableTypeID
----------------------------
1     1
2     1
3     2
4     3
5     2
6     3

Persons
ID    Name
---------------------------
1     Joe Smith
2     Mary Belcher
3     Norma Grey


Roles
ID    RoleName
-----------------------------
1     Bus Boy
2     Waiter
3     Server
4     Dealer
5     Pit Boss
6     Programmer


TableRoles
TableID    RoleID    PersonID
-----------------------------
1          1         1
//etc
//etc

, ( , )

Answer 2

. :

Person     PersonRole     Role      Table
------     ----------     ----      -----
Id*        PersonId*      Id*       Id*
Name       RoleId*        Name      Name
                          TableId

...

select dining_table from Dining_Tables where 
      bus_boy = "Joe Smith" or
      waiter = "Joe Smith" or 
      server = "Joe Smith"
union
select poker_table from Poker_Tables where 
      dealer = "Joe Smith" or 
      pit_boss = "Joe Smith"
union
select computer_table from Computer_Tables where 
      programmer = "Joe Smith"

Answer 3

, , :

SELECT U.user_id, d.dining_table, p.poker_table, c.computer_table
FROM Users U
LEFT JOIN Dining_Table D
  ON D.bus_boy = U.user_id OR D.waiter = U.user_id or D.server = U.user_id
LEFT JOIN Poker_Table P
  ON P.dealer = U.user_id OR P.pit_boss = U.user_id
LEFT JOIN Computer_Table C
 ON C.programmer = U.user_id
WHERE U.Name = 'Joe Smith'

-- only return rows with at least one table match
-- or leave off to always return the user)
AND NOT (
  d.dining_table IS NULL 
  AND p.poker_table IS NULL 
  AND c.computer_table IS NULL
)

, , ( null).

If you really need a direct list for only one user, an allied approach may be preferable. However, if this query is run for a list or users, I think my approach is better.