Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to show name instead of ID in same table multiple times with empty values?

I have 2 tables. Table 1:

ID, ReceiveID, ProcessID, DeliverID
1      1          1          
2      1          2          3
3      3

Table 2:

ID, Name
1     Jack
2     Mary
3     Paul

I want to get:

Table1ID, Receiver, Processor, Delivered
1           Jack      Jack       
2           Jack      Mary       Paul
3           Paul

Can anyone please help me to build the query?

I tried:

SELECT a.Table1ID, b.name, bb.name, bc.name
FROM Table1 as a
JOIN Table2 as b ON a.ReceiveID = b.ID
JOIN Table2 as bb ON a.ProcessID = b.ID
JOIN Table2 as bc ON a.DeliverID = b.ID

but did not work.

Tags:

2

Answers


  1. 0

    You must use left join to allow the rows to be preserved also when some id are null:

    select tab1.id, rec.name, proc.name, delv.name
from tab1
left join tab2 rec on tab1.receive_id = rec.id
left join tab2 proc on tab1.process_id = proc.id
left join tab2 delv on tab1.deliver_id = delv.id
    Login or Signup to reply.
  2. 0

    you need to use the table aliases

    SELECT a.Table1ID, b.name, bb.name, bc.name
FROM Table1 as a
JOIN Table2 as b ON a.ReceiveID = b.ID
JOIN Table2 as bb ON a.ProcessID = bb.ID
JOIN Table2 as bc ON a.DeliverID = bc.ID
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search