i'm trying to get a list of bands a user might like based on common artists they share in their list with other users.. i have 4 tables one that wholes all the bands, one holds all users, one holds all that users selected bands, and the last one holds all relations between the band and the users other choices..

here is the SQL statement I am using..

SELECT band.name
FROM band
INNER JOIN related ON band.id = related.child_id
LEFT JOIN favorites ON related.parent_id = favorites.band_id
WHERE favorites.user_id = 1
GROUP BY band.id

this works but the problem is it also brings up all the bands that user likes.. it's not filtering them out.. for example say JIM likes the strokes, coldplay, led zeppelin, and the who and JON likes coldplay, oasis, and the verve and Fred likes the strokes and the white stripes..

I want it to display oasis, the verve, and the white stripes. Instead I'm getting the strokes, coldplay, led zeppelin, the who, oasis, the verve, and the white stripes..

any suggestiongs??



DB Structures

user table
id
username

band table
id
bandname

favorite table
id
user_id
favorite_id

related table
id
favorite_id
parent_id
child_id