MySQL Join When Record in Joined Table Does Not Exist

Question

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?

Answer 1

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.