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

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    You can try using a correlated subquery to achieve the desired result. Here is an example query that uses a correlated subquery:

    select
    TablePC.name,
    (select name from TableParty where no = TablePC.party19) as a,
    (select name from TableParty where no = TablePC.party24) as b
from TablePC;

    By using a correlated subquery like the one above, you should be able to get the output you desire.

    A, X, X
B, X, Y
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search