Re: [GENERAL] Left join help

[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 AND 
members.id = 1
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 
> 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 
 > 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.kla...@aklaver.com

"id","locked_title","locked_content","date","created_at","updated_at","locked_cta","locked_placeholder","locked_attachment_url","track_id","locked_attachment_name","locked_attachment_mime_type","locked_deadline_time","locked_character_minimum","promo_bg_url","promo_color_bg","promo_color_highlight","locked_character_minimum_message","locked_character_minimum_title","promo_title","promo_headline","promo_prize_title","promo_email_bg_url","feedback_headline","feedback_content","feedback_attachment_image_url","feedback_attachment_audio_url","feedback_attachment_video_url","feedback_audio_headline","promo_order"
5,"What's wrong with this advertising?","https://s27.postimg.org/ea9kcfr4j/Best_NYT.png"; width=""484"" height=""401"" />
For reference, here are the other 2 we looked at in this campaign:
Just one question for today's mission:
Why don't you have a subscription to the NYT?
Have you ever considered the benefit, value, or future desired state?
If not, what friction do you think is getting in the way?
*Notice theirCTA (button) improving as well","2017-06-09","2016-12-07 15:20:44.43092","2017-02-27 13:35:57.841557","Locked cta","Locked placeholder","",7,"Locked attachment name","image",,,"https://content.linkedin.com/content/dam/business/sales-solutions/global/en_US/blog/2017/01/Wolf-of-Wall-Street.jpg","#82FFA3","#FF722B",,,"promo_title","promo_headline","promo_prize_title","https://ucarecdn.com/a59f992c-2049-47ce-9123-8ea104a4a146/","THIS IS FEEDBACK","Below are two sample Facebook advertisements fromhttp://www.ashandanvil.com";>Ash  Anvil:

http://postimg.org/image/9xms10asz/";>The first reads, ""We're excited to announce that we are semi-finalists for Hatch Detroit's annual contest! With your vote, we can win $50,000 to open our first store in Detroit! Winners are selected based on a public vote and we would love your support. Voting only takes a few seconds and there's no sign-up required. You can vote once per day on both the Hatch website and Facebook Page"" 
http://postimg.org/image/c0vqblos3/";>The second reads, ""Our Fall Collection has arrived featuring premium fabrics, an extra-soft feel, and the same great price!"" with a big shirt front and 

Re: [GENERAL] Left join help

[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 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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Left join help

[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 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 
> wrote:

SELECT *
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

Re: [GENERAL] Left join help

[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 ...

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.

Re: [GENERAL] Left join help

[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 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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Left join help

[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 
> wrote:

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 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.

Re: [GENERAL] Left join help

[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 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.