Re: [GENERAL] Left join help

2017-06-24 Thread Arup Rakshit
Hello Adrian, The way John taught me this query with CTE and then how that can be using with JOIN really helped me to understand. The query below is from John and it worked. SELECT * FROM missions LEFT JOIN submissions INNER JOIN members ON submissions.member_id = members.id A

Re: [GENERAL] Left join help

2017-06-24 Thread Adrian Klaver
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 ex

Re: [GENERAL] Left join help

2017-06-24 Thread Arup Rakshit
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. SELECT

Re: [GENERAL] Left join help

2017-06-23 Thread David G. Johnston
On Fri, Jun 23, 2017 at 3:17 PM, Arup Rakshit wrote: > Hi David, > > Can you tell me how can I add ordering between LEFT and INNER JOIN. I > think also that is where I am wrong, but not sure how to correct the > ordering. > ​Generally... FROM tbl1 LEFT JOIN (tbl2 JOIN ​tbl3 ON ...) tbl2_3 ON ..

Re: [GENERAL] Left join help

2017-06-23 Thread Paul Jungwirth
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 O

Re: [GENERAL] Left join help

2017-06-23 Thread Arup Rakshit
Hi David, Can you tell me how can I add ordering between LEFT and INNER JOIN. I think also that is where I am wrong, but not sure how to correct the ordering. Thanks, A On Jun 24, 2017, at 3:18 AM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Friday, June 23, 2017, Arup Ra

Re: [GENERAL] Left join help

2017-06-23 Thread David G. Johnston
On Friday, June 23, 2017, Arup Rakshit wrote: > > FROM "missions" LEFT JOIN submissions ON submissions.mission_id = > missions.id > INNER JOIN members ON members.id = submissions.member_id > Quick look here but having submissions as part of the inner join with members defeats the attempt at left

[GENERAL] Left join help

2017-06-23 Thread Arup Rakshit
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