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@xxxxxxxxx
<mailto:wishdev@xxxxxxxxx>> 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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
