Re: [GENERAL] Left join help

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 > > wrote:
>>
>> SELECT *
>> FROM missions
>> LEFT JOIN submissions
>> INNER JOIN members
>> ON submissions.member_id =members.id ANDmembers.id 
>> = 1
>> ONmissions.id = submissions.mission_id
>> WHERE missions.track_id = 7
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com