public inbox for [email protected]
help / color / mirror / Atom feedLeft join syntax error
9+ messages / 4 participants
[nested] [flat]
* Left join syntax error
@ 2024-05-18 12:52 Rich Shepard <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Rich Shepard @ 2024-05-18 12:52 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Left join syntax error
@ 2024-05-18 13:19 Shammat <[email protected]>
parent: Rich Shepard <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Shammat @ 2024-05-18 13:19 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Left join syntax error
@ 2024-05-18 14:46 Rich Shepard <[email protected]>
parent: Shammat <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: Rich Shepard @ 2024-05-18 14:46 UTC (permalink / raw)
To: [email protected]
On Sat, 18 May 2024, Shammat wrote:
> 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
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Left join syntax error
@ 2024-05-18 14:49 Ray O'Donnell <[email protected]>
parent: Rich Shepard <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Ray O'Donnell @ 2024-05-18 14:49 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; [email protected]
On 18/05/2024 15:46, Rich Shepard wrote:
> On Sat, 18 May 2024, Shammat wrote:
>
>> 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
>
> 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;
>
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 protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Left join syntax error
@ 2024-05-18 14:49 Adrian Klaver <[email protected]>
parent: Rich Shepard <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: Adrian Klaver @ 2024-05-18 14:49 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; [email protected]
On 5/18/24 07:46, Rich Shepard wrote:
> On Sat, 18 May 2024, Shammat wrote:
>
>> 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
>
> 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;
... LEFT JOIN companies as c ON c.company_nbr = p.company_nbr;
>
> Thanks for the suggestion.
>
> Regards,
>
> Rich
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Left join syntax error
@ 2024-05-18 15:01 Rich Shepard <[email protected]>
parent: Ray O'Donnell <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: Rich Shepard @ 2024-05-18 15:01 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Left join syntax error
@ 2024-05-18 15:03 Adrian Klaver <[email protected]>
parent: Rich Shepard <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2024-05-18 15:03 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; [email protected]
On 5/18/24 08:01, Rich Shepard wrote:
> 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.
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.
>
> Thanks,
>
> Rich
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Left join syntax error
@ 2024-05-18 15:04 Ray O'Donnell <[email protected]>
parent: Rich Shepard <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: Ray O'Donnell @ 2024-05-18 15:04 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; [email protected]
On 18/05/2024 16:01, Rich Shepard wrote:
> 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.
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.
>
> Thanks,
>
> Rich
>
>
--
Raymond O'Donnell // Galway // Ireland
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Left join syntax error
@ 2024-05-18 15:38 Rich Shepard <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Rich Shepard @ 2024-05-18 15:38 UTC (permalink / raw)
To: [email protected]
On Sat, 18 May 2024, Adrian Klaver wrote:
> 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.
Thanks, Adrian. I mis-read your original post.
Regards,
Rich
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-05-18 15:38 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-18 12:52 Left join syntax error Rich Shepard <[email protected]>
2024-05-18 13:19 ` Shammat <[email protected]>
2024-05-18 14:46 ` Rich Shepard <[email protected]>
2024-05-18 14:49 ` Ray O'Donnell <[email protected]>
2024-05-18 15:01 ` Rich Shepard <[email protected]>
2024-05-18 15:03 ` Adrian Klaver <[email protected]>
2024-05-18 15:38 ` Rich Shepard <[email protected]>
2024-05-18 15:04 ` Ray O'Donnell <[email protected]>
2024-05-18 14:49 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox