left/right/inner join on mysql statement

Question

I have the following SQL query :

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_images AS bandImage ON band.id = bandImage.bid
LEFT JOIN band_followers AS follower ON follower.bid = band.id
RIGHT JOIN users AS user ON user.id = follower.uid
INNER JOIN user_info AS userInfo ON userInfo.uid = user.id
LEFT JOIN user_images AS userImage ON user.id = userImage.uid
WHERE ((band.activated = 1) AND (user.activated = 1)) AND (bandInfo.language = 'fr')
ORDER BY band.name

'users' table contains all users (one row = one user)

'user_info' table contains all info about an user (one row = one user)

'user_images' table contains all image about users (one row = one user)

'bands' table contains all music bands (one row = one band)

'band_info' table contains all info about a band (multiple row for one band due to the language)

'band_images' table contains all image about bands (one row = one band)

'band_followers' table contains all relations between users and bands (one row = one relation so there are many row containing the same band id but not the same user id)

I would like to retrieve all bands with their info (INNER JOIN) EVEN IF the band id isn't in the band_followers table. I want to retrieve too all bands when the band id is in the band_followers table with an INNER JOIN on user id with the user's info (INNER JOIN) EVEN IF he doesn't have any image.

My problem is just the JOIN keyword.. I don't really know if I have to use LEFT or RIGHT JOIN

Thanks !

UPDATE ANSWER :

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_images AS bandImage ON band.id = bandImage.bid
LEFT JOIN band_followers AS follower ON band.id = follower.bid
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_images AS userImage ON user.id = userImage.uid
WHERE (band.activated = 1) AND (bandInfo.language = 'fr')
ORDER BY band.name

Thank you all

score 2 · Accepted Answer · answered Jun 19 '15 at 22:31

You should use LEFT JOIN before users and user_info, just like you do before band_followers. LEFT JOIN means to return all the rows of the previous tables even if they don't match anything in the table you're joining with. RIGHT JOIN means to return all rows of the table you're joining with. So if you use RIGHT JOIN users, you'll get all users, not just the users who are following the band.

You should also put the test user.activated = 1 into the ON clause of LEFT JOIN users. Otherwise, you'll filter out all the bands that have no followers, because they don't have any followers with user.activated.

score 1 · Answer 2 · answered Jun 19 '15 at 22:16

1

Simple: "left join" means take the left item and if there is a matching right one, attach it.

answered Jun 19 '15 at 22:16

guest

11
1

score 1 · Answer 3 · answered Jun 19 '15 at 22:17

1

Wherever you have the statements like

EVEN IF the band id isn't in the band_followers table.

OR

EVEN IF he doesn't have any image

Try using a OUTER JOIN like either LEFT OUTER JOIN OR RIGHT OUTER JOIN per the your requirement.

answered Jun 19 '15 at 22:17

Rahul

76,197
13
71
125

1

That's what he's using. `LEFT JOIN` and `RIGHT JOIN` are always outer joins, the word `OUTER` is optional. – Barmar Jun 19 '15 at 22:26
@Barmar, yes true but I believe what OP is saying is that, he is confused whether he should use OUTER/INNER join. That's what I am trying to point out in answer. – Rahul Jun 19 '15 at 22:28
Sorry, I misread your answer, I thought you were just telling him to change `LEFT JOIN` to `LEFT OUTER JOIN`. – Barmar Jun 19 '15 at 22:31

score 1 · Answer 4 · edited May 23 '17 at 12:29

1

this question might help you out What is the difference between "INNER JOIN" and "OUTER JOIN"?

but the best answer there was this picture (in my opinion)

https://i.stack.imgur.com/1UKp7.png

edited May 23 '17 at 12:29

Community

1
1

answered Jun 19 '15 at 22:20

WWZee

494
2
8
23

left/right/inner join on mysql statement

4 Answers4