postgresql multi join syntax

Question

SELECT sessions_compare.*
    FROM archive_sessions as f_session, sessions_compare
    LEFT JOIN archive_sessions as s_session ON (s_session.id = sessions_compare.second_session_id)
    LEFT JOIN consols as f_consol ON (f_session.console_id = f_consol.id)

    where sessions_compare.first_session_id = f_session.id

after executing a get error like

ERROR: invalid reference to FROM-clause entry for table "f_session" LINE 13:
LEFT JOIN consols as f_consol ON (f_session.console_id =...

HINT: There is an entry for table "f_session", but it cannot be referenced from this part of the query.

When i switch places and have from like sessions_compare, archive_sessions as f_session i get error like

ERROR: invalid reference to FROM-clause entry for table "sessions_compare" LINE 4: ... archive_sessions as s_session ON (s_session.id = sessions_c...

HINT: There is an entry for table "sessions_compare", but it cannot be referenced from this part of the query.

And only thing that work is

SELECT sessions_compare.*
    FROM sessions_compare
    LEFT JOIN archive_sessions as s_session ON (s_session.id = sessions_compare.second_session_id)

    ,archive_sessions as f_session

    LEFT JOIN consols as f_consol ON (f_session.console_id = f_consol.id)
    where sessions_compare.first_session_id = f_session.id

And my question is it normal ?? Im young in Postgresql in mysql when using multiple join from tables needed to be in ()

Using shorter correlation name tags for the table entries would probaby simplify reading. — wildplasser
– wildplasser, Commented Apr 18, 2012 at 9:58
BTW: when you edit your question, it is adviasble not to remove fragments of it on which people can have reacted. I spent some time repairing the silly query, and all that you did is remove it from the question. — wildplasser
– wildplasser, Commented Apr 18, 2012 at 13:01
Its not about the query, its about syntax. I make this query work, I was wounder if its normal to create query this way — kskaradzinski
– kskaradzinski, Commented Apr 18, 2012 at 15:25

Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-04-18 10:37:09Z

2

Yes, that's documented behavior in PostgreSQL.

In any case JOIN binds more tightly than the commas separating FROM items.

That means PostgreSQL acts like it builds its working table by evaluating the JOIN clauses before evaluating a comma-list in the FROM clause.

Best practice is generally considered to be to always use JOIN clauses; never put more than one table name in the FROM clause.

answered Apr 18, 2012 at 10:37

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wildplasser · Accepted Answer · 2012-04-18 10:27:37Z

2

Changing the main query to a JOIN (and adding/shortening some correlation names) seems to fix the problem.

DROP SCHEMA fuzz CASCADE;
CREATE SCHEMA fuzz;
SET search_path='fuzz';

create table archive_sessions ( id INTEGER NOT NULL
 , console_id INTEGER NOT NULL, game_id INTEGER NOT NULL);
create table sessions_compare (first_session_id INTEGER NOT NULL
 , second_session_id INTEGER NOT NULL);
create table consols (id INTEGER NOT NULL);
create table games (id INTEGER NOT NULL);
create table localizations ( consols_id INTEGER NOT NULL, bars_id INTEGER NOT NULL);
create table bars ( id_bars INTEGER NOT NULL, area_id INTEGER NOT NULL);
create table areas ( id_areas INTEGER NOT NULL );

SELECT sco.*
    FROM archive_sessions as ase
    JOIN sessions_compare sco ON sco.first_session_id = ase.id

    LEFT JOIN archive_sessions as ss ON (ss.id = sco.second_session_id)
    LEFT JOIN consols as sc ON (ss.console_id = sc.id)
    LEFT JOIN games as sg ON (ss.game_id = sg.id)
    LEFT JOIN localizations as sl ON (sc.id = sl.consols_id)
    LEFT JOIN bars as sb ON (sl.bars_id = sb.id_bars)
    LEFT JOIN areas as sa ON (sb.area_id = sa.id_areas)



    LEFT JOIN consols as fc ON (ase.console_id = fc.id)
    LEFT JOIN games as fg ON (ase.game_id = fg.id)
    LEFT JOIN localizations as fl ON (fc.id = fl.consols_id)
    LEFT JOIN bars as fb ON (fl.bars_id = fb.id_bars)
    LEFT JOIN areas as fa ON (fb.area_id = fa.id_areas)

    -- WHERE sco.first_session_id = ase.id
        ;

answered Apr 18, 2012 at 10:27

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

2 Comments

kskaradzinski Over a year ago

But I dont want to change this query, I want it to stay like I showed it

wildplasser Over a year ago

The only thing I did (except for some renaming) was replacing the FROM x, y by the equivalent FROM x JOIN y construct, and putting the WHERE clause into the ON condition. (and that is exactly what @Catcall suggests, BTW)

Collectives™ on Stack Overflow

postgresql multi join syntax

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related