CODEWITHSUNDEEP
phpmysqljoin
Dan
Dan

Reputation: 11

MySQL / PHP joining single record to multiple records

I have a table called ABC with 3 fields : id1, id2, id3 with data :

id1 | id2 | id3
________________
1   |  5  | 3
3   |  4  | 2
2   |  3  | 1

I have a second table called XYZ with 2 fields, id and title with data : 

id |  title
______________
1  |  title_1 
2  |  title_2 
3  |  title_3
4  |  title_4
5  |  title_5

The ids in table ABC match the ids of each record in table XYZ. What id like to do is join the ids in table ABC with those in table XYZ and display the titles in a row. So if the first two records in table ABC is outputted it will look like this:

title_1, title_5, title_3
title_3, title_4, title_2

Outputting in PHP is fine I'm just a little lost with the SQL. I've been trying to use left joins but I haven't got very far with it. Any help would be much appreciated.

Upvotes: 0

Views: 192

Answers (1)

Arnaud Le Blanc
Arnaud Le Blanc

Reputation: 99939

You can join the same table multiple times with different join conditions:

SELECT xyz1.title as title1, xyz2.title as title2, xyz3.title as title3
FROM ABC
JOIN XYZ as xyz1 ON xyz1.id = ABC.id1
JOIN XYZ as xyz2 ON xyz2.id = ABC.id2
JOIN XYZ as xyz2 ON xyz3.id = ABC.id3

This shoud correctly output

title_1, title_5, title_3
title_3, title_4, title_2

Upvotes: 3

Related Questions