Обсуждение: [GENERAL] Left join help
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 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)
The above query really not showing any output. Any help to solve this?
Tables are posted here: http://dpaste.com/1EXF4KQ
Thanks,
A
On Friday, June 23, 2017, Arup Rakshit <aruprakshit1987@outlook.com> wrote:
FROM "missions" LEFT JOIN submissions ON submissions.mission_id = missions.idINNER 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 joining it with missions. You might need to add parentheses to put the joins in the correct order, or something. Why it gives zero results we cannot say without a self-contained example.
David J.
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 <david.g.johnston@gmail.com> wrote:On Friday, June 23, 2017, Arup Rakshit <aruprakshit1987@outlook.com> wrote:FROM "missions" LEFT JOIN submissions ON submissions.mission_id = missions.idINNER JOIN members ON members.id = submissions.member_idQuick look here but having submissions as part of the inner join with members defeats the attempt at left joining it with missions. You might need to add parentheses to put the joins in the correct order, or something. Why it gives zero results we cannot say without a self-contained example.David J.
> 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
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 ...
In short, put parentheses around the join and give it an alias.
There are ordering rules that can be considered too but frankly I've never learned them and haven't noticed their absence. The above is the explicit way to do things and explicit is generally better.
David J.
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 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
====
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> wrote:SELECT *FROM missionsLEFT JOIN submissionsWHERE missions.track_id = 7
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
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
ON missions.id = submissions.mission_id
WHERE missions.track_id = 7
Result is attached.
When I added CASE expression to the above query it was giving me the error as I mentioned in previous email. I managed the CASE expression query with CTE as below:
with member_submissions as ( SELECT submissions.* FROM submissions INNER JOIN members ON submissions.member_id = members.id AND members.id = 1 ) SELECT *, CASE WHEN member_submissions.id IS NULL THEN 'incompleted' ELSE 'completed' END AS mission_status FROM missions LEFT JOIN member_submissions ON missions.id = member_submissions.mission_id WHERE missions.track_id = 7
But still would like to know why it didn’t work with JOIN.
Thanks,
- A
On Jun 24, 2017, at 10:30 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: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