3

How I can join two tables when record in joined table doesn't exist. I'm tried something like this:

SELECT 
    Confessions.ID, Confessions.WriteTitle, Confessions.WriteArea,
    Confessions.POST_DATE, Votes.Type
FROM Confessions LEFT OUTER JOIN Votes ON Confessions.ID = Votes.PostID 
WHERE ForUser = :ForUser AND Status = :Status AND Votes.IP_Check = :IP_Check

but when record from votes with PostID like ID in Confessions doesn't exist MySQL gives back empty result for this record. isthere any other way?

1 Answer 1

6

A WHERE-condition on the inner table will change the the result to an Inner Join instead. You need to move this condition to the ON:

SELECT 
    Confessions.ID, Confessions.WriteTitle, Confessions.WriteArea,
    Confessions.POST_DATE, Votes.Type
FROM Confessions LEFT OUTER JOIN Votes 
  ON Confessions.ID = Votes.PostID 
 AND Votes.IP_Check = :IP_Check
WHERE ForUser = :ForUser AND Status = :Status 

As a rule of thumb: Normally conditions on the Outer table are in WHERE while condition on the Inner table are in ON.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.