Multiple joins on same table

Codes here !

Moderators: macek, egami, gesf

z5
New php-forum User
New php-forum User
Posts: 3
Joined: Tue Dec 17, 2002 1:23 pm

Multiple joins on same table

Postby z5 » Tue Dec 17, 2002 1:45 pm

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
New php-forum User
New php-forum User
Posts: 13
Joined: Tue Nov 12, 2002 12:11 pm
Location: Ukraine
Contact:

Postby Elena Mitovska » Thu Dec 19, 2002 12:00 am

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
New php-forum User
New php-forum User
Posts: 3
Joined: Tue Dec 17, 2002 1:23 pm

Postby z5 » Thu Dec 19, 2002 3:14 am

Elena Mitovska wrote: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.


Return to “mySQL & php coding”

Who is online

Users browsing this forum: Yahoo [Bot] and 0 guests