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