How To Construct Outer Join

[fox12]

I need to retrieve data from three tables using an outer join. I created a sql like the following:
SELECT a.ID, a.name, b.payment, c.description
FROM table1 a, table2 b, table3 c
WHERE (a.ID = b.ID
AND b.active = 'Y'
AND c.ID = a.cust_code (+)
It returns that the SQL command does not end properly. What is wrong with this?

Thanks

 

[lewisp]

The error you have stems from the lack of a closing bracket, or the presence of the opening one right after the WHERE.

You may also have the outer join predicate on the wrong side too. I suspect you need to put the (+) on C.ID and not A.CUST_CODE.

 

[dbomrrsm]

SELECT a.ID, a.name, b.payment, c.description
FROM table3 c outer join table1 a
on c.ID = a.cust_code
outer join table2 b
on a.ID = b.ID , table3 c
WHERE  b.active = 'Y'

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dbomrrsm]

sorry

SELECT a.ID, a.name, b.payment, c.description
FROM table3 c right outer join table1 a
on c.ID = a.cust_code
left outer join table2 b
on a.ID = b.ID
WHERE  b.active = 'Y'

depends though on what results you want - this will give you everything from table a

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[fox12]

Thanks. I used the "LEFT OUTER JOIN" to get the correct data.

 

[dbomrrsm]

Your welcome - glad it worked !

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]