CODEWITHSUNDEEP
mysqljoinleft-join
DasBeasto
DasBeasto

Reputation: 2272

Joining two mysql tables where clause may not exist

Ok that title hardly describes the issue. But Im stuck on how to solve this, so Ill just try to describe it.

I have two mySql table: listings, userVotes

Listings has fields (id, description, votes) userVotes has fields (userId, listingsId)

Well Im trying to get the join of these tables so the result is (id, description, votes, userId, listingsId, vote)

So statement would normally be "select * join on id = listings id" (paraphrased)

But this led to problems because I still need the listing to show even if there was no userVote for it.

So I changed it to "select * left join" and that allowed me to retrieve the listing even if there was no userVote associated with it.

But this led to another problem. I need to have the constraint "where userId = 'foo'"; on the result. But this doesnt work because again it leaves out the listings with no userId.

So essentially I need the statement: "Select * from listings l left join userVote u on l.id = u.listingId where if exists userId = 'foo'"

Is it doable?

Upvotes: 0

Views: 53

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

You can just move the condition to the ON clause of the left join to not make it restrict the resulting rows;

SELECT * 
FROM listings l 
LEFT JOIN userVote u 
  ON l.id = u.listingId 
 AND u.userId = 'foo'

Any restriction you put in the WHERE clause will remove results, while any you put in the ON clause of a left join will only set the userVote to NULL if not matched.

Upvotes: 2

Related Questions