Suppose i've got two tables: one with musiccd's and one with musicgroups (this is just example to explain my problem):
- 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)
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.