MySQL / PHP joining single record to multiple records

Question

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.

Arnaud Le Blanc · Accepted Answer · 2011-08-24 11:28:38Z

3

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

edited Aug 24, 2011 at 11:28

answered Aug 24, 2011 at 10:42

Arnaud Le Blanc

100k24 gold badges211 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wesley van Opdorp Over a year ago

You should select title as an alias aswell, or they will overrule each other.

Dan Over a year ago

This works perfectly, thank you. I was in the middle of posting a reply with the aliases in it but seeing as the code was updated I saw no point. Thank you very much for your help, much appreciated :)

Collectives™ on Stack Overflow

MySQL / PHP joining single record to multiple records

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related