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

wildplasser wildplasser · Accepted Answer · 2012-04-18T10:27:37

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
        ;

postgresql multi join syntax

2 Answers