Mysql Join three tables based on column value

Question

I have three tables

usrs

ID   Name
--------
1   Mark Dav
2   Dan Fos

cust

ID   Name
--------
1   Jane Cue
2   Mary Tie

msgs

ID   Frm_ID  To_ID  Msg    frm_cat to_cat
--------
1     1      1      hello  usr      cus
1     1      2      There  usr      cus
1     2      1      hello  cus      usr
1     2      2      hello  cus      usr

How do I join the tables so that I can get following results.

msgs

ID   Frm_ID  To_ID   Frm_name To_name   Msg    frm_cat to_cat
------------------------------------------------
1     1      1       Mark Dav Jane Cue  hello  usr      cus
1     1      2       Mark Dav Mary Tie  There  usr      cus
1     2      1       Mark Tie Mark Dav  hello  cus      usr
1     2      2       Mark Tie Dan Fos   hello  cus      usr

my attempt

id as id,
  m.frm_id,
  m.to_id,
  m.msg,
  f.name frm_name,
  t.name to_name,
from msgs m 
  left join usrs on f.id = m.from_id AND m.from_cat = 'usr' OR cust f on f.id = m.frm_id AND m.from_cat = 'cus'
  left join usrs t on t.id = m.to_id AND m.from_cat = 'usr' OR cust f on f.id = m.fom_id AND m.from_cat = 'cus';

but i got series of errors

Preferably, the tables should share keys so that you can join the table on them and get a correct and meaningful result. How is the table msgs is connected to the cust table and how is it connected to the usrs table? — MaD, Feb 27 '17 at 13:49
the cust and users are connected to msgs based on from_id and to_id — Smith, Feb 27 '17 at 13:51
In case Frm_ID on msgs table is a foreign key to usrs table ID column. Then you can do something like this: select * from msgs left join usrs on (msgs.Frm_ID = usrs.ID); — MaD, Feb 27 '17 at 13:53
You should merge the tables `usrs` and `cust` into one table with an additional column `role` or `type` to avoid a lot of problems in the future. — Paul Spiegel, Feb 27 '17 at 14:03
@PaulSpiegel that suggestion is no good for my need because a user with email a@b.com can exist as a usr in usrs and as a customer in cust — Smith, Feb 27 '17 at 17:39
@Smith I don't see a problem there. That person can have two entries in the new table. Or the more correct way: Define a many-to-many relation between persons and roles. — Paul Spiegel, Feb 27 '17 at 18:24
@PaulSpiegel There are some orther details i left out, such as cust only have abut 6 columns while usrs have 18columns. If i should join them, there would be a lot of Null values, and some other issues — Smith, Feb 27 '17 at 21:35
There is a solution for that too. Hint: Search for "Common Columns Table" and "Table Inheritance". The folowing Q&A also describes a similar problem with a solution: http://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database — Paul Spiegel, Feb 28 '17 at 12:17

score 0 · Accepted Answer · answered Feb 27 '17 at 13:54

You could use a union all subquery to stack usrs and cust together and then use the from_cat and to_cat fields to decide what values to join on. Unfortunately, MySQL doesn't support CTEs, so unless you want to create a view for this, you could just use the same union all query twice, once per join:

SELECT m.id, frm_id, to_id, msg, frm_name, to_name, frm_cat, to_cat
FROM   msgs m
JOIN   (SELECT id, name AS frm_name, 'usr' AS cat
        FROM   usrs
        UNION ALL 
        SELECT id, name AS frm_name, 'cus' AS cat
        FROM   cust) f ON m.frm_id = f.id AND f.frm_cat = f.cat
JOIN   (SELECT id, name AS to_name, 'usr' AS cat
        FROM   usrs
        UNION ALL 
        SELECT id, name AS to_name, 'cus' AS cat
        FROM   cust) f ON m.to_id = f.id AND t.to_cat = f.cat

Mysql Join three tables based on column value

1 Answers1