Re: Left join syntax error

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