MySQL: Multi-column join across 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 rows has globally unique table id associated with it: (dining_table, poker_table, computer_table) The other columns store the First/Last name of the person fulfilling the roll.

In my model, a single individual can be doing multiple things. For example, Joe Smith could be simultanously sitting at a computer_table as a programmer, sitting at a poker_table as a dealer, and waiting on a dining_table as a waiter.

My question is this: I want a query that will allow me to retrieve all the table_ids for a given person. Specifically, the query would then return a list of table_ids that Joe Smith is currently at.

I could do something along these lines:

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";

However, that's three seperate queries and I'd really prefer to avoid doing that if possible. Can I do it in one query using joins?

Answer 1

If you want to return it in a single query you could use a UNION statement that would join those three queries into one.

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"

Although it is three statements, it would be returned as a single result set.

If you separated your "jobs" into a separate table, it would make querying a lot easier.

I could imagine something along the lines of:

Tables, TableTypes, Persons, Roles, TableRoles

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

This would make all of your querying very simple and straightforward and also makes it scalable (so you can add more roles to each table without mucking your database)

Answer 2

Your data model is sub-optimal. Consider:

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

That being said...

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

I think you should start with your user table and then join to the other tables from there:

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
)

This will give you one row per user, indicating which table the user is at (or null).

If you really want a straight list for a single user only, then the union approach may be preferred. However, if this query will be run for a roster or users, I think my approach is better.