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
