Multiple joins on same table

[z5]

hi!

Suppose i've got two tables: one with musiccd's and one with musicgroups (this is just example to explain my problem):

table musiccd:
- musiccd_id (autonum)
- musiccd_name (varchar)
- musiccd_group1 (integer) (points to musicgroup_id in table musicgroup, will always be filled in as there is always at least one musicgroup which has made the cd)
- musiccd_group2 (integer) (points to musicgroup_id in table musicgroup) (this can be empty and it is only filled in when two groups did a cooperation on the same CD)

table musicgroup:
- musicgroup_id
- musicgroup_name

Suppose that every musiccd added will have at least one musicgroup (so musiccd_group1 will always be filled in and pointing to musicgroup_id in table musicgroups).

So far so good. I've got this working with inner joins.

Problem starts when two groups cooperate on the same CD (this can happen but not a lot of times). And now i'm running into problems. Suppose that i want to have a list with all musiccd's and the groups who have made it. How do i put this into my SQL.

SELECT musiccd.musiccd_name, musiccd.group_name
FROM musiccd INNER JOIN musicgroup ON
musiccd.musiccd_group1 = musicgroup.musicgroup_id

But what about the second group?

Thanks for any help as this has me puzzled.

[Elena Mitovska]

This might work as you need:

SELECT musiccd.musiccd_name, musicgroup.group_name, musicgroup2.group_name
FROM musiccd INNER JOIN musicgroup ON
musiccd.musiccd_group1 = musicgroup.musicgroup_id
LEFT OUTER JOIN musicgroup musicgroup2 ON
musiccd.musiccd_group2 = musicgroup2.musicgroup_id

[z5]

This might work as you need:

SELECT musiccd.musiccd_name, musicgroup.group_name, musicgroup2.group_name
FROM musiccd INNER JOIN musicgroup ON
musiccd.musiccd_group1 = musicgroup.musicgroup_id
LEFT OUTER JOIN musicgroup musicgroup2 ON
musiccd.musiccd_group2 = musicgroup2.musicgroup_id

Thanks for the answer. I understand your solution but there is one question: i don't have a musicgroup2.group_name (i only have one table with musicgroups)! This is exactly my problem. If i had two tables with musicgroups, then indeed your solution would have worked.