patternsqlMinor
MySQL Join When Record in Joined Table Does Not Exist
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:
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?
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_Checkbut 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:
As a rule of thumb: Normally conditions on the Outer table are in
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 = :StatusAs 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 = :StatusContext
StackExchange Database Administrators Q#104812, answer score: 6
Revisions (0)
No revisions yet.