CODEWITHSUNDEEP
mysqlsqljoin
JiFus
JiFus

Reputation: 968

SQL how to use multiple joins on the same table

I'm stuck with a tricky question that I'm unable to solve myself. This is what I'm trying to achieve.

The desired output

Is this even possible? I tried following query already, but I'm not getting what I want.

SELECT t1.ID, info.start_bt1, info.start_bt2, t1.ANT as BT1_VL1, t2.ANT as BT1_VL2, t3.ANT as BT2_VL1, t4.ANT as BT2_VL2 
FROM antwoorden t1 
LEFT JOIN info ON t1.ID = info.ID 

LEFT JOIN antwoorden t2
ON t1.ID = t2.ID
AND t2.BT = 1 AND t2.VL = 2

LEFT JOIN antwoorden t3
ON t1.ID = t3.ID
AND t3.BT = 2 AND t3.VL = 1

LEFT JOIN antwoorden t4
ON t1.ID = t4.ID
AND t4.BT = 2 AND t4.VL = 2

WHERE t1.BT = 1 AND t1.VL = 1

The problem is I'm only getting the row with ID 2 (from the desired result). Does anyone know why I'm only getting the row with ID 2 and not all 4 of them?

Edit

I updated the picture so it's a bit clearer what I mean. Also, the ID is the ID given to a person. As you can see in the picture, this means that one person can have multiple rows. What I'm trying to do is to group all the data gathered from a person by their ID. I know this way is not a good way to store data, but I can't help it since somebody else made this, and I can't change it.

Another edit: here is a sql fiddle

Upvotes: 0

Views: 125

Answers (1)

Tom Mac
Tom Mac

Reputation: 9853

Try it the other way round. Start with the info table and then LEFT JOIN multiple times to antwoorden:

select i.id,i.start_bt1,i.start_bt2,
a1.ant as "bt1_vl1",
a2.ant as "bt1_vl2",
a3.ant as "bt2_vl1",
a4.ant as "bt2_vl2"
from info i
left join antwoorden a1 on a1.id = i.id and a1.vl=1 and a1.bt=1
left join antwoorden a2 on a2.id = i.id and a2.vl=2 and a2.bt=1
left join antwoorden a3 on a3.id = i.id and a3.vl=1 and a3.bt=2
left join antwoorden a4 on a4.id = i.id and a4.vl=2 and a4.bt=2;

Upvotes: 1

Related Questions