Subject: Left join syntax error

From: Rich Shepard <[email-masked]>
Date: Sat, 18 May 2024 05:52:34 -0700 (PDT)

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.

Please show me what I've done incorrectly.

TIA,

Rich

---

From: Shammat <[email-masked]>
Date: Sat, 18 May 2024 15:19:41 +0200

> 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

---

From: Rich Shepard <[email-masked]>
Date: Sat, 18 May 2024 07:46:39 -0700 (PDT)

>  LEFT JOIN companies as c ON c.company_nbr = p.company_nbr

Shammat,

I tried this with this result:

ERROR:  missing FROM-clause entry for table "c"
LINE 3: LEFT JOIN companies ON c.company_nbr = p.company_nbr;

Thanks for the suggestion.

Regards,

Rich

---

From: Ray O'Donnell <[email-masked]>
Date: Sat, 18 May 2024 15:49:02 +0100

> LINE 3: LEFT JOIN companies ON c.company_nbr = p.company_nbr;

You need to include the alias for the table also - see "...from 
companies as c..." in Shammat's example.

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
[email-masked]

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 18 May 2024 07:49:12 -0700

> LINE 3: LEFT JOIN companies ON c.company_nbr = p.company_nbr;

... LEFT JOIN companies as c ON c.company_nbr = p.company_nbr;

-- 
Adrian Klaver
[email-masked]

---

From: Rich Shepard <[email-masked]>
Date: Sat, 18 May 2024 08:01:53 -0700 (PDT)

On Sat, 18 May 2024, Ray O'Donnell wrote:

> You need to include the alias for the table also - see "...from companies as 
> c..." in Shammat's example.

Ray,

That didn't work:
bustrac-# FROM people as p, companies as c
bustrac-# LEFT JOIN companies as c ON c.company_nbr = p.company_nbr;
ERROR:  table name "c" specified more than once

so I tried only the alias on the join line:
bustrac-# LEFT JOIN c ON c.company_nbr = p.company_nbr;
ERROR:  relation "c" does not exist
LINE 3: LEFT JOIN c ON c.company_nbr = p.company_nbr;
                   ^
and that didn't work either.

Thanks,

Rich

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 18 May 2024 08:03:42 -0700

> and that didn't work either.

The query needs to be:

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;

Only reference companies as c once.

---

From: Ray O'Donnell <[email-masked]>
Date: Sat, 18 May 2024 16:04:44 +0100

> and that didn't work either.

Hi Rich,

Look again at Shammat's example! -

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

NB - "... from people as p left join companies as c on ...." - i.e. the 
companies table (or its alias c) is only specified once.

HTH,

Ray.

---

From: Rich Shepard <[email-masked]>
Date: Sat, 18 May 2024 08:38:46 -0700 (PDT)

> Only reference companies as c once.

Thanks, Adrian. I mis-read your original post.

Regards,

Rich