Search code examples
mysql

Join tables on two column

I have tables with following structure

TablePC

no name party19 party24
1 A 1 1
2 B 1 2

party19 and party24 columns are Foreign key constraint to TableParty.no

TableParty

no name
1 X
2 Y

My query

select
    TablePC.name
   ,TableParty.name as A
   ,TableParty.name as B
from TablePC 
join TableParty on TableParty.no = TablePC.party19 and TableParty.no=TablePC.party24;

I require the output as
A X X
B X Y

I am not able to get this output. Any help is appreciated.

WR

Solution

  • You need two joins, one for each foreign key column:

    SELECT tpc.name, tp1.name AS A, tp2.name AS B
FROM TablePC tpc
LEFT JOIN TableParty tp1
   ON tp1.no = tpc.party19
LEFT JOIN TableParty tp2
   ON tp2.no = tpc.party24;

    Note: I use left joins above because, in the event that a name in the TablePC column does not have matches in TableParty for both party19 and party24 that row would still be in the result set. Inner joins would remove such non matching records in TablePC.