sub query on left join which gves duplicate rows

[Sami Jnih]

Hello everybody,

I'm not a wizard of MySQL queries so here is my problem

I have 7 tables :

users
user_info
user_image

bands
band_info
band_image

relationship table between users and bands : band_followers

users table contains one unique user
user_info table contains one unique user's info
user_image table contains one unique user's image

bands table contains one unique music band
band_info table contains many band's info due to the language (description only in french and in english for the moment)
band_image table contains one unique band's image

My query is :

I need to retrieves all activated bands with their info (INNER JOIN band_info).
I need to retrieve the band's image if exists (LEFT JOIN band_image).
I need to retrieve all relations between bands and users if exists (LEFT JOIN band_followers).
I need to retrieve all activated users which are in the relation table if exists (LEFT JOIN users).
I need to retrieve the user's info if exists (LEFT JOIN user_info).
I need to retrieve the user's image if exists (LEFT JOIN user_image).

So my query is :

SELECT band.id bid, band.name, bandInfo.summary, bandImage.url bandImage, user.username, user.online, userImage.url userImage
FROM bands AS band
INNER JOIN band_info AS bandInfo ON band.id = bandInfo.bid
LEFT JOIN band_image AS bandImage ON band.id = bandImage.bid
LEFT JOIN band_followers AS follower ON follower.bid = band.id
LEFT JOIN users AS user ON user.id = follower.uid AND user.activated = 1
LEFT JOIN user_info AS userInfo ON userInfo.uid = user.id
LEFT JOIN user_image AS userImage ON user.id = userImage.uid
WHERE (band.activated = 1) AND (bandInfo.language = 'fr')
ORDER BY band.name

This works fine because I have 3 results.

1. A band with the ID of 1 which have 1 follower with the ID of 1
2. A band with the ID of 1 which have 1 follower with the ID of 2
3. A band with the ID of 2 which have 1 follower with the ID of 1


Now I need to limit users which follows bands because my website works with paginated results. You can understand that I can't display 100 users on my website who follows the band "Children Of Bodom" but display only 10 per page.

My query now looks like :

SELECT band.id bid, band.name, bandInfo.summary, bandImage.url bandImage, user.username, user.online, userImage.url userImage
FROM bands AS band
INNER JOIN band_info AS bandInfo ON band.id = bandInfo.bid
LEFT JOIN band_image AS bandImage ON band.id = bandImage.bid
LEFT JOIN band_followers AS follower ON follower.bid = band.id
LEFT JOIN users AS user ON user.id IN (SELECT * FROM (SELECT user.id FROM users AS user, bands AS band, band_followers AS follower WHERE user.id = follower.uid) AS temp) AND user.activated = 1
LEFT JOIN user_info AS userInfo ON userInfo.uid = user.id
LEFT JOIN user_image AS userImage ON user.id = userImage.uid
WHERE (band.activated = 1) AND (bandInfo.language = 'fr')
ORDER BY band.name

But this query returns me duplicates lines like:

1. A band with the ID of 1 which have 1 follower with the ID of 1
2. A band with the ID of 1 which have 1 follower with the ID of 1
3. A band with the ID of 1 which have 1 follower with the ID of 2
4. A band with the ID of 1 which have 1 follower with the ID of 2
5. A band with the ID of 2 which have 1 follower with the ID of 1
6. A band with the ID of 2 which have 1 follower with the ID of 2

The user 2 is not following the band 2 but I have it.. and there is only the user 1 and 2 who follow the band 1

Thank you for the help if somebody can help me