Hi,
I have this relationship Track has many missions. Missions has many submissions. Each Submission has one member and one mission. Say I have track id 7. I want to find out which missions under track 7 are submitted by John ( a user id say 3 ) and which are not yet. I tried a query, but it is not giving me any result. Can anyone help me pls?
SELECT missions.*,
CASE WHEN submissions.id IS NULL THEN 'incompleted'
ELSE 'completed' END AS mission_status
FROM "missions" LEFT JOIN submissions ON submissions.mission_id = missions.id
INNER JOIN members ON members.id = submissions.member_id
WHERE (members.id = 1 AND missions.track_id = 7)
The above query really not showing any output. Any help to solve this?
Thanks,
A