Question 114498 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – What is wrong with this JOIN syntax?

If I run this SQL code in my PostgreSQL database, it works fine:

select 
    e.type_id,
    s.copy_destination || ''
FROM 
    ebooks.ebooks e, ebooks.static_text s
WHERE
    e.status_id = 1

But when I add the join, everything goes pear-shaped. So if I run:

select 
    e.type_id,
    s.copy_destination || ''
FROM 
    ebooks.ebooks e, ebooks.static_text s
join 
    ebooks.types y
on 
    e.type_id = y.type_id
WHERE
    e.status_id = 1

I get this error message:

ERROR:  invalid reference to FROM-clause entry for table "e"
LINE 11:     e.type_id = y.type_id
             ^
HINT:  There is an entry for table "e", but it cannot be referenced from this part of the query.
SQL state: 42P01
Character: 159

I’ve tried using the full reference (ebooks.eboooks.type_id) instead of just e, but nothing changed. I still got the "invalid reference to FROM-clause entry for table ‘e’" – note that it still thinks I’m trying to reference table ‘e’.

What am I doing wrong?

Tags:

2

Answers


  1. 0 
        select 
        e.type_id,
        s.copy_destination ||
        ''
    FROM 
        ebooks.static_text s Join  ebooks.ebooks e
on s.ref_id = e.id
    join 
        ebooks.types y
    on 
        e.type_id = y.type_id
    WHERE
        e.status_id = 1

    Change the query to this format then it should work.
    ref_id and id are the names of joining columns of 2 tables. Change that way because the order of tables in query and multiple joining columns (more than 1 column get join) in both tables can be the reason for this error.

    Login or Signup to reply.
  2. 0

    The reason is that a comma (,) in the FROM list is almost, but not quite the same as a CROSS JOIN. Explicit join syntax binds before commas or, in other words, the comma separates stronger. So the reference to e.type_id is placed where that table is still out of scope. See:

    You can replace the comma with an otherwise equivalent CROSS JOIN, to silence the error:

    SELECT e.type_id, s.copy_destination || ''
FROM   ebooks.ebooks e
CROSS  JOIN ebooks.static_text s               -- !!!
JOIN   ebooks.types y ON e.type_id = y.type_id
WHERE  e.status_id = 1;

    Rearranged to make clearer:

    SELECT e.type_id, s.copy_destination || ''
FROM   ebooks.ebooks      e
-- JOIN   ebooks.types       y USING (type_id)
CROSS  JOIN ebooks.static_text s    --   now you might as well use a comma again!
WHERE  e.status_id = 1

    I commented out the join to ebooks.types completely as it seems to be dead freight – assuming referential integrity is guaranteed with an FK constraint.

    The issue remains that the table static_text is joined to the rest without condition, which produces a Cartesian Product of both sets. Typically not what you want …

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search