On 06/24/2017 08:01 AM, Arup Rakshit wrote:
> 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...
>
>
>> On Jun 24, 2017, at 4:53 AM, John W Higgins <wishdev@gmail.com
>> <mailto:wishdev@gmail.com>> wrote:
>>
>> 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
>
--
Adrian Klaver
adrian.klaver@aklaver.com
