From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Left join syntax error |
Date: | 2024-05-18 15:18:34 |
Message-ID: | 775a2a40-9353-9275-8d7e-1e4c1fccad21@appl-ecosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, 18 May 2024, Erik Wienhold wrote:
> Yes, Rich probably just wants the left join.
Eric,
You're correct: I want certain colums from the people table with their
company name from the companies table.
> But I wonder if the implicit cross join syntax ("FROM peoples, companies")
> should actually produce this error because the explicit cross join
> works:
>
> SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email, c.company_name
> FROM people as p
> CROSS JOIN companies as c
> LEFT JOIN companies ON c.company_nbr = p.company_nbr;
Aha! I ignored the cross join because I don't need all columns from both
tables. And it worked here (slowly) with a Ryzen7 2700 CPU and 64G RAM.
> If you just move the LEFT JOIN condition to the WHERE clause it works as
> well, which indicates that the aliases from the implicit cross join do
> work as if it has been an explicit cross join:
>
> SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email, c.company_name
> FROM people as p, companies as c
> LEFT JOIN companies ON true
> WHERE c.company_nbr = p.company_nbr;
This didn't work as well; too many repeats for each row in people.
Thank you for a valuable lesson, Eric.
Best regards,
Rich
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-05-18 15:23:04 | Re: utf8 vs UTF-8 |
Previous Message | Tom Lane | 2024-05-18 15:16:58 | Re: Left join syntax error |