Re: Left join syntax error

Am 18.05.24 um 14:52 schrieb Rich Shepard:
> It's been a _very_ long time since I wrote a SQL script and, despite looking
> at my SQL books and web pages, I don't know how to fix the error.
>
> The three line script is:
> -----
> 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 c.company_nbr = p.company_nbr;
> -----
>
> and psql responds:
> ERROR:  invalid reference to FROM-clause entry for table "p"
> LINE 3: LEFT JOIN companies ON c.company_nbr = p.company_nbr;
>                                                ^
> HINT:  There is an entry for table "p", but it cannot be referenced from this part of the query.

Don't put the second table in the FROM part

SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email, c.company_name
FROM people as p
LEFT JOIN companies as c ON c.company_nbr = p.company_nbr