Re: Left join help

[Arup Rakshit <aruprakshit1987@xxxxxxxxxxx>]

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 <adrian.klaver@xxxxxxxxxxx> 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@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

"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?","<p><img src=""https://s27.postimg.org/ea9kcfr4j/Best_NYT.png""; width=""484"" height=""401"" /></p>
<p><em>For reference, here are the other 2 we looked at in this campaign:</em></p>
<p><strong>Just one question for today's mission:</strong></p>
<p>Why don't you have a subscription to the NYT?</p>
<p><em>Have you ever considered the benefit, value, or future desired state?</em></p>
<p>If not, what friction do you think is getting in the way?</p>
<p>&nbsp;<em>*Notice their&nbsp;CTA (button) improving as well</em></p>","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","<p>Below are two sample Facebook advertisements from&nbsp;<a href=""http://www.ashandanvil.com"";>Ash &amp; Anvil</a>:</p>
<ol>
<li><a href=""http://postimg.org/image/9xms10asz/"";>The first reads</a>, ""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. &nbsp;Voting only takes a few seconds and there's no sign-up required. &nbsp;You can vote once per day on both the Hatch website and Facebook Page"" &nbsp;</li>
<li><a href=""http://postimg.org/image/c0vqblos3/"";>The second reads</a>, ""Our Fall Collection has arrived featuring premium fabrics, an extra-soft feel, and the same great price!"" with a big shirt front and center. &nbsp;</li>
</ol>
<p>Neither of these messages <em>fundamentally</em> benefit the audience.</p>
<p><strong>They're all about Ash &amp; Anvil.&nbsp;</strong></p>
<ul>
<li>""WE'RE excited to announce""</li>
<li>""You can vote (for US) once per day... in&nbsp;two different places""</li>
<li>""OUR fall collection has arrived...""</li>
</ul>
<p><strong>It's not speaking to the (naturally self-involved) perspective of their target market.</strong></p>
<p><em>How can you speak more directly with them?</em></p>
<p>Today's mission is to take these two existing ads and rewrite them with the customer and the benefit in mind.&nbsp;</p>
<ol>
<li>How can winning $50,000 in this contest benefit their customers? What you offer them&nbsp;<em>if</em> they vote? What other incentive is going to be more enticing than just asking them for something?</li>
<li>How does the arrival of the fall catalog change the life of their ideal customer?&nbsp;<em>How could&nbsp;it benefit them socially, personally, or professionally?</em>&nbsp;</li>
</ol>",,,,,0
7,"Lets Redo Uber's Marketing","<p>Below are two sample Facebook advertisements from&nbsp;<a href=""http://www.ashandanvil.com"";>Ash &amp; Anvil</a>:</p>
<ol>
<li><a href=""http://postimg.org/image/9xms10asz/"";>The first reads</a>, ""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. &nbsp;Voting only takes a few seconds and there's no sign-up required. &nbsp;You can vote once per day on both the Hatch website and Facebook Page"" &nbsp;</li>
<li><a href=""http://postimg.org/image/c0vqblos3/"";>The second reads</a>, ""Our Fall Collection has arrived featuring premium fabrics, an extra-soft feel, and the same great price!"" with a big shirt front and center. &nbsp;</li>
</ol>
<p>Neither of these messages <em>fundamentally</em> benefit the audience.</p>
<p><strong>They're all about Ash &amp; Anvil.&nbsp;</strong></p>
<ul>
<li>""WE'RE excited to announce""</li>
<li>""You can vote (for US) once per day... in&nbsp;two different places""</li>
<li>""OUR fall collection has arrived...""</li>
</ul>
<p><strong>It's not speaking to the (naturally self-involved) perspective of their target market.</strong></p>
<p><em>How can you speak more directly with them?</em></p>
<p>Today's mission is to take these two existing ads and rewrite them with the customer and the benefit in mind.&nbsp;</p>
<ol>
<li>How can winning $50,000 in this contest benefit their customers? What you offer them&nbsp;<em>if</em> they vote? What other incentive is going to be more enticing than just asking them for something?</li>
<li>How does the arrival of the fall catalog change the life of their ideal customer?&nbsp;<em>How could&nbsp;it benefit them socially, personally, or professionally?</em>&nbsp;</li>
</ol>","2017-06-08","2016-10-20 14:03:47.776491","2016-12-07 15:13:54.266185","  2016   December   5 Locked cta","Locked placeholder","https://ucarecdn.com/921f3fab-d76a-4b49-b882-7f52f49b9566/",7,"Locked attachment name","image",,,,,"#009888",,,"promo_title","promo_headline","promo_prize_title","https://ucarecdn.com/a59f992c-2049-47ce-9123-8ea104a4a146/",,,,,,,0
9,"Offer from Warby Parker","<p>Locked content</p>","2017-06-07","2016-12-07 15:21:22.933459","2016-12-07 15:21:22.933459","Locked cta","Locked placeholder","",7,"Locked attachment name","image",,,"http://totalsportscomplex.com/wp-content/uploads/2014/09/baseball-pic.jpg",,,,,"promo_title","promo_headline","promo_prize_title","https://ucarecdn.com/a59f992c-2049-47ce-9123-8ea104a4a146/",,,,,,,0

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