[RESOLVED] outer join with sql

kakki

hello,
i'm in need of a join statement, that outer-joins three tables ( A, b, c )
A is the "main" table( all elements should come up )
b and c are only secondary ( and should only come up, when there's an equivalent
in table A )

i got an outer-join for two tables from the net which is:
select * from A,b WHERE A.id=b.id OR b.id IS NULL
( thats functioning )
now i want to expand that to the three tables.

any idea how that statement will look like ???

Roger_Ramjet

Well, full marks for searching the answer first. 🙂

Unfortunately that syntax is naff. Best one can say is it must be for an old old db engine.: or [shudders] ODBC :eek:

For mysql we write it thus


select * from a LEFT JOIN b on a.id=b.id LEFT JOIN c on a.id=c.id

// sometimes a db engine would require

select a.*, b.*, c.* from a LEFT JOIN b on a.id=b.id LEFT JOIN c on a.id=c.id

// but not mysql

kakki

old old db-engine???
i have to work on several db-engines ( like mysql, postgres, oracle .... whatever )
and - since now - i haven't used any of the extended sql-syntax
( after several hours of debugging LIMIT and OFFSET - statements in postgres/mysql )

mrhappiness

(A.id=b.id OR b.id IS NULL)
OR
(A.id=c.id OR c.id IS NULL)

like that maybe?

kakki

i tried something similar( i think i tried all permutations for that statment,
like ( ... ) OR ( ... ); ( ... ) AND ( ... )
without any suitable solution,
but if you arer sure, that this will work, i'll try it out

Roger_Ramjet

Just use the LEFT JOIN unless your db definitely does not support it - which I doubt. That's what I meant - does not support left joins == old old.

Edit **

Simple, quick, and it will work.

kakki

well, thats not done so easy,

i ported the scripts from postgres to mysql using a function ( named "babelfish" 🆒 )
the application is still using/generating postgres-sql commands, then they are parsed
with the babelfish to get the "modified" mysql - statement which is then
executed

kakki

mmmmhhhh

i had to restart several times, but i didnt get the right statement

any further suggestions???

Roger_Ramjet

No.

kakki

oki