Joining a different table based on field value

[GuitarGod]

Hi all,

 

I have two different user tables - one for regular users (user_id, username), and one for administrators (admin_id, admin_username).

 

I also have a table called messages (message, user_type, user_id).

 

I'd like to query messages so that if user_type is equal to 0, then the user_id field in messages table will match up to a user_id row in the users table. However, if user_type in the messages table is equal to 1, then the user_id field in that table should match up to admin_id in the administrators table.

 

I hope that isn't too confusing. I've tried CASE and JOIN but just can't seem to get it to work.

 

Sincere thanks to anyone who can help,

 

Regards

[Barand]

Have you tried this method

 

http://forums.phpfreaks.com/topic/283707-joiningif-3-tables/?do=findComment&comment=1457559

 

Which raises the question - why do you insist on storing the same type of data in separate tables instead of a single table with a type column? Could save you from these problems.

[GuitarGod]

Hi Barand,

 

Thank you for your response. I tried working on one of your previous answers but for some reason it wouldn't work. Perhaps you can see what I'm doing wrong:

 

 

SELECT m.user_id, m.message,
                                    CASE user_type
                                          WHEN 0 THEN u.username
                                          WHEN 1 THEN a.admin_username
                                    END AS username
                                    FROM messages m
                                          LEFT JOIN users u ON m.user_id = u.user_id
                                          LEFT JOIN administrators a ON m.user_id = a.admin_id

 

The users table consists of exactly those two fields above (user_id, username), where as the admin table consists of many more fields than written above, so I figured having a separate table would use less space than using the admin table to store two pieces of data.

[Barand]

This worked for me

mysql> SELECT m.message, m.user_type
    ->     , CASE m.user_type
    ->         WHEN 0 THEN u.username
    ->         WHEN 1 THEN a.admin_username
    ->         END as Username
    ->     FROM messages m
    ->     LEFT JOIN users u ON m.user_id = u.user_id
    ->     LEFT JOIN administrators a ON m.user_id = a.admin_id;
+-----------------+-----------+----------+
| message         | user_type | Username |
+-----------------+-----------+----------+
| message user 1  |         0 | User 1   |
| message admin 1 |         1 | Admin 1  |
| message user 2  |         0 | User 2   |
| message admin 2 |         1 | Admin 2  |
| message user 3  |         0 | User 3   |
| message admin 3 |         1 | Admin 3  |
+-----------------+-----------+----------+

and so did yours