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? Tables are posted here: http://dpaste.com/1EXF4KQ Thanks, A