> 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)
I always think about JOINs as being implemented from top to bottom, and
you can track the current result rows in your head. So in your case you
start with one row for each mission. Then you add zero or more rows for
each submission. Because it's an outer join you keep rows even if they
don't match.
Then you join to members, but if there is no match, you drop the row
from the result. But since there are no members with a NULL id (I hope)
any row where submissions.member_id is NULL have no matches, so all the
unmatched rows you kept from the outer join fall out here.
Since you know that a submission never has more than one member, it
would be safe to use a LEFT OUTER JOIN in both places, and that will let
you preserve incomplete missions all the way through to the final result.
Furthermore, your WHERE drops everything where members.id is not 1. So
again you are throwing away incomplete missions. I guess you need to
permit anything where members.id is 1 or NULL.
Finally, your intent seems to be to get one row per mission, but if a
mission has several submissions you will get duplicates. So maybe for
complete/incomplete you should just use EXISTS with a correlated subquery.
I hope that helps. Good luck!
Paul
