joining 3 tables sql join

Question

I have 3 tables users(name, email), reviews(review, entity, user) and votes(vote, entity, user), I want to make a query which returns all the reviews given for a particular entity, and vote from votes table if the user have voted for that entity, else null should be there at the place of vote. The query I wrote is

select users.name as name, 
reviews.review as review, 
votes.vote as vote 
from 
users join reviews on users.email=reviews.user 
left join votes on users.email=votes.user 
where reviews.entity='entity_id'

But this query is resulting multiple rows for some reviews, I made it working using group by, by am unable to understand the behavior of left join,

Carl · Accepted Answer · 2012-07-28 15:32:22Z

1

You are not joining votes for any specific entity, try the following:

select users.name as name, 
reviews.review as review, 
votes.vote as vote 
from 
users join reviews on users.email=reviews.user 
left join votes on users.email=votes.user and reviews.entity = votes.entity
where reviews.entity='entity_id'

answered Jul 28, 2012 at 15:32

Carl

3341 silver badge15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dani herrera Over a year ago

Remember group by clause as OP says: "But this query is resulting multiple rows for some reviews, I made it working using group by..."

Carl Over a year ago

I would have to see the working version, but I doubt the results would be correct by adding a group by. The query would still be looking for reviews with the entity 'entity_id', but votes for any entity from the same user.

Collectives™ on Stack Overflow

joining 3 tables sql join

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related