The way John taught me this query with CTE and then how that can be using with JOIN really helped me to understand. The query below is from John and it worked.
When I added CASE expression to the above query it was giving me the error as I mentioned in previous email. I managed the CASE expression query with CTE as below:
with member_submissions as (
SELECT submissions.*
FROM submissions
INNER JOIN members ON submissions.member_id = members.id
AND members.id = 1
)
SELECT *, CASE WHEN member_submissions.id IS NULL THEN 'incompleted' ELSE 'completed' END AS mission_status
FROM missions
LEFT JOIN member_submissions ON missions.id = member_submissions.mission_id
WHERE missions.track_id = 7
But still would like to know why it didn’t work with JOIN.
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.
Can you show what works?
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
I am not how Postgres would determine which ON refers to which JOIN here?
To get back to your original request would the below work?:
SELECT missions.*, CASE WHEN sub.id IS NULL THEN 'incompleted' ELSE 'completed' END AS mission_status FROM missions LEFT JOIN (SELECT * FROM submissions WHERE member_id = 1) AS sub ON missions.mission_id = sub.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...
SELECT * FROM missions LEFT JOIN submissions INNER JOIN members ON submissions.member_id =members.id <http://members.id/>ANDmembers.id <http://members.id/>= 1 ONmissions.id <http://missions.id/>= submissions.mission_id WHERE missions.track_id = 7
