HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

MySQL Join When Record in Joined Table Does Not Exist

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
joinedexistjoinmysqlrecorddoeswhennottable

Problem

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?

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#104812, answer score: 6

Revisions (0)

No revisions yet.