Horton
Menu

Thread

  1. Left join syntax error

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-18T12:52:34Z

    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

  2. Re: Left join syntax error

    shammat@gmx.net — 2024-05-18T13:19:41Z

    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

  3. Re: Left join syntax error

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-18T14:46:39Z

    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

  4. Re: Left join syntax error

    Ray O'Donnell <ray@rodonnell.ie> — 2024-05-18T14:49:02Z

    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
ray@rodonnell.ie

  5. Re: Left join syntax error

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-05-18T14:49:12Z

    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
adrian.klaver@aklaver.com

  6. Re: Left join syntax error

    Erik Wienhold <ewie@ewie.name> — 2024-05-18T15:00:05Z

    On 2024-05-18 15:19 +0200, Shammat wrote:
> 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

Yes, Rich probably just wants the left join.

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;

But I'm not even sure if implicit and explicit cross join are
semantically equivalent.  The docs on FROM [1] sort of imply that:

"If multiple sources are specified, the result is the Cartesian product
 (cross join) of all the sources."

Maybe it's only meant that both syntaxes are equivalent regarding the
result, and that it does not extend to aliases of those FROM items.

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;

[1] https://www.postgresql.org/docs/current/sql-select.html#SQL-FROM

-- 
Erik

  7. Re: Left join syntax error

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-18T15:01:53Z

    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

  8. Re: Left join syntax error

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-05-18T15:03:42Z

    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
adrian.klaver@aklaver.com

  9. Re: Left join syntax error

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-18T15:04:13Z

    On Sat, 18 May 2024, Adrian Klaver wrote:

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

Adrian,

Tried that:
bustrac-# LEFT JOIN companies as c ON c.company_nbr = p.company_nbr;
ERROR:  table name "c" specified more than once

Thanks,

Rich

  10. Re: Left join syntax error

    Ray O'Donnell <ray@rodonnell.ie> — 2024-05-18T15:04:44Z

    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
ray@rodonnell.ie

  11. Re: Left join syntax error

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-05-18T15:12:00Z

    On 5/18/24 08:04, Rich Shepard wrote:
> On Sat, 18 May 2024, Adrian Klaver wrote:
> 
>> ... LEFT JOIN companies as c ON c.company_nbr = p.company_nbr;
> 
> Adrian,
> 
> Tried that:
> bustrac-# LEFT JOIN companies as c ON c.company_nbr = p.company_nbr;
> ERROR:  table name "c" specified more than once

Show the complete query.

Take the error message as correct, you are specifying 'companies as c' 
more then once.

> 
> Thanks,
> 
> Rich
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  12. Re: Left join syntax error

    David G. Johnston <david.g.johnston@gmail.com> — 2024-05-18T15:12:35Z

    On Saturday, May 18, 2024, Erik Wienhold <ewie@ewie.name> wrote:

> On 2024-05-18 15:19 +0200, Shammat wrote:
> > 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
>
> Yes, Rich probably just wants the left join.
>
> 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;
>
> But I'm not even sure if implicit and explicit cross join are
> semantically equivalent.  The docs on FROM [1] sort of imply that:
>

Too lazy to find the docs right now but what you are observing is basically
an operator precedence effect.  The comma join hasn’t happened at the time
the left join is evaluated and so other tables in the comma join cannot
appear in the on clause of the left join.  Placing everything inside a
single from slot and moving the conditions to the where clause removes
changes the precedence aspect so that the cross join does indeed evaluate
prior to the left join.

I’m content with not pointing out this possible gotcha in the documentation.

David J.

  13. Re: Left join syntax error

    Erik Wienhold <ewie@ewie.name> — 2024-05-18T15:14:32Z

    I wrote:
> 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;

On second thought it looks like that (companies as c LEFT JOIN companies)
actually is the second FROM item.  Adding parenthesis to the explicit
cross join version gives the same error:

    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
        );

So the comma in the FROM item list has lower precedence than the join
operators.

-- 
Erik

  14. Re: Left join syntax error

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-05-18T15:16:58Z

    Erik Wienhold <ewie@ewie.name> writes:
> 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;

> But I'm not even sure if implicit and explicit cross join are
> semantically equivalent.

Well, they do the same thing, but JOIN binds tighter than comma.
So in one case you have effectively

    FROM people as p CROSS JOIN
         (companies as c LEFT JOIN companies ON c.company_nbr = p.company_nbr)

and "p" is not within the scope of the JOIN/ON clause.
The other way is effectively

    FROM (people as p CROSS JOIN companies as c)
         LEFT JOIN companies ON c.company_nbr = p.company_nbr;

which is syntactically legal, although it probably doesn't do
what you wanted.

If memory serves, MySQL got this basic syntactic detail wrong
for years, as a result of which there's (still) a tremendous amount
of confusion on the net about what is the syntactic precedence in
FROM clauses.

			regards, tom lane

  15. Re: Left join syntax error

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-18T15:18:34Z

    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

  16. Re: Left join syntax error

    David G. Johnston <david.g.johnston@gmail.com> — 2024-05-18T15:27:19Z

    On Sat, May 18, 2024 at 7:49 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> 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;
>
>
You failed at copy-and-paste.  If you use the exact query provided it will
indeed work.

If you want to explain why you thought writing in the company table twice
into the FROM clause of the query was a good idea maybe we can help you
unlearn that bad belief.  Otherwise feel free to just take the answer
you've been given.

David J.

  17. Re: Left join syntax error

    Erik Wienhold <ewie@ewie.name> — 2024-05-18T15:30:36Z

    On 2024-05-18 17:12 +0200, David G. Johnston wrote:
> Too lazy to find the docs right now but what you are observing is basically
> an operator precedence effect.  The comma join hasn’t happened at the time
> the left join is evaluated and so other tables in the comma join cannot
> appear in the on clause of the left join.  Placing everything inside a
> single from slot and moving the conditions to the where clause removes
> changes the precedence aspect so that the cross join does indeed evaluate
> prior to the left join.

Thanks David.  The docs on table expressions clarify the precedence:

https://www.postgresql.org/docs/16/queries-table-expressions.html#QUERIES-FROM

I'm using SQL for 17 years now and yet I still forget that joins are
table expressions m(

-- 
Erik

  18. Re: Left join syntax error

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-18T15:38:46Z

    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

  19. Re: Left join syntax error

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-18T15:40:09Z

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

> 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.

Ray,

Yes, I did mis-read that.

Many thanks,

Rich

  20. Re: Left join syntax error

    Rich Shepard <rshepard@appl-ecosys.com> — 2024-05-18T15:41:26Z

    On Sat, 18 May 2024, Adrian Klaver wrote:

> Show the complete query.
> Take the error message as correct, you are specifying 'companies as c' more 
> then once.

Adrian,

I saw that but didn't know how to specify the alias only one time.

Thanks,

Rich