Sql - Join

[janjacina]

Hi,

I have a problem...

I know that oracle use (+) instead JOIN...

SELECT * FROM tabA, tabB, tabC
WHERE tabA.b_id = tabB.id (+)
AND ???? -- I need join tabB with tabC but null values (from A in column b_id) have to be in result...


SQL>
SELECT * FROM tabA,tabB,tabC
WHERE tabA.b_id = tabB.id (+)
AND tabB.c_id = tabC.id;
this command doesnt work correct...

[DaPi]

If I understand correctly that you want all the rows from tabA:

SELECT * FROM tabA,tabB,tabC
WHERE tabA.b_id = tabB.id(+)
AND tabB.c_id = tabC.id(+);

You need the second (+) because there will be rows from tabC "missing" as well.

[janjacina]

doesn't work...

i thought about it like you, but doesn't work

[DaPi]

Well - it works for me:

  1  select p.swpersonid, m.swpersonid, a.rlaccountid
  2  from  sw_person p, rl_m2m_account_rel m, rl_account a
  3  where p.swpersonid = 123456789
  4  and   p.swpersonid = m.swpersonid(+)
  5* and   m.rlaccountid = a.rlaccountid(+)

SWPERSONID SWPERSONID RLACCOUNTID
---------- ---------- -----------
 123456789

What happens for you?

[DaPi]

Just a thought - if you put addional conditions on columns in tabB or tabC, then you need to allow for NULL values:

SELECT * FROM tabA,tabB,tabC
WHERE tabA.b_id = tabB.id(+)
AND tabB.c_id = tabC.id(+)
AND (tabB.otherCol = 'Valid' OR tabB.otherCol IS NULL);