Thread: MULTIPLE OUTER JOIN SYNTAX :-( can't quess it

MULTIPLE OUTER JOIN SYNTAX :-( can't quess it

From
Constantin Teodorescu
Date:
Please excuse for my lack of imagination building SQL selects with OUTER
JOINS but something's unclear for me ...

I have 3 tables, t0, t1 and t2.

Tables are:
authors (alias t0) with (id int2, name text);
books (alias t1) with (author int2, title text);
nicknames (alias t2) with (id int2, nickname text);

I want a select query with them with 2 outer joins like that

t0.id = t1.author
t0.id = t2.id

Tried :
select t0.name, t1.title, t2.nickname from authors t0 left outer JOIN
books t1 ON (t0."id"=t1."author") , authors t0 left outer join nicknames
t2 on (t0."id"=t2."id") 

but it says "author table declared more than once".

with just 1 table it worked for me

Please, could you help me quickly showing me the right syntax?
I'm working to add outer joins to visual query builder in PgAccess.

Constantin Teodorescu
Braila, ROMANIA


Re: MULTIPLE OUTER JOIN SYNTAX :-( can't quess it

From
Tom Lane
Date:
Constantin Teodorescu <teo@flex.ro> writes:
> I want a select query with them with 2 outer joins like that
> t0.id = t1.author
> t0.id = t2.id
> Tried :
> select t0.name, t1.title, t2.nickname from authors t0 left outer JOIN
> books t1 ON (t0."id"=t1."author") , authors t0 left outer join nicknames
> t2 on (t0."id"=t2."id") 
> but it says "author table declared more than once".

select t01.name, t01.title, t2.nickname from   (authors t0 left outer join books t1 on (t0."id"=t1."author")) t01 left
outerjoin   nicknames t2 on (t01."id"=t2."id") ;
 

There are variants but that's the basic idea...
        regards, tom lane