Re: Left join help

Hi,

Thanks everyone for taking time to explain this. I tried to add a case statement and getting errors. Can you tell me how should I add a column to mark which mission is completed and which is not. My try is not working. Instead of the new CASE expression, the query works as expected.

SELECT missions.*, CASE WHEN submissions.id IS NULL THEN 'incompleted' ELSE 'completed' END AS mission_status
FROM missions
LEFT JOIN submissions
INNER JOIN members
ON submissions.member_id = members.id AND members.id = 1
ON missions.id = submissions.mission_id
WHERE missions.track_id = 7

====

ERROR: missing FROM-clause entry for table "missions"
LINE 1: SELECT missions.*, CASE WHEN submissions.id IS NULL THEN 'in...

On Jun 24, 2017, at 4:53 AM, John W Higgins <wishdev(at)gmail(dot)com<mailto:wishdev(at)gmail(dot)com>> wrote:

SELECT *
FROM missions
LEFT JOIN submissions
INNER JOIN members
ON submissions.member_id = members.id<http://members.id/> AND members.id<http://members.id/> = 1
ON missions.id<http://missions.id/> = submissions.mission_id
WHERE missions.track_id = 7