CODEWITHSUNDEEP
sqloracle-databasejoin
byle.05
byle.05

Reputation: 196

Ora SQL Query: joining without references

I am trying to achieve some logic on Oracle by using simple query and feeling stuck on it. The thing is that I cannot use PL-SQL and this is giving me some headached.

I have three tables with below values

I am trying to get something like:

SELECT T1.CODE,T2.CODE,T3.VALUE
FROM TABLE1 T1
JOIN TABLE2 T2 ON T1.REF = T2.CODE
JOIN TABLE3 T3 ON T2.REF = T3.CODE
WHERE T1.CODE = XXXXX

Result for XXXX = 98

98,2,CCC

Whenever the parameter XXXXX is 99,98,96,95 it returns what I was expecting but the logic I need doesnt work for 97.

My requirement says that in case i cannot find a link in Table2 then I should use always DEF in Table3 and leave unlinked values as NULL. Something like:

Result for XXXX = 97

97,NULL,AAA

I think it could be achieved in a not very "clean" way by using CASE statements but this is an example in which the number of columns shown is very minimal. In my real case it is extremelly bigger... So I want to try to avoid using CASE statements as it would raise the complexity of it a lot.

I tried with different methods but my low experience on Oracle cannot deep so much :)

Any way to achieve this without using PLSQL neither those CASE?

Upvotes: 3

Views: 85

Answers (1)

sgeddes
sgeddes

Reputation: 62841

If I'm understanding correctly, you need to use an outer join instead. You can then use COALESCE to return the value associated with "DEF" if T2.REF is NULL:

SELECT T1.CODE,
       T2.CODE, 
       T3.VALUE
FROM TABLE1 T1
    LEFT JOIN TABLE2 T2 ON T1.REF = T2.CODE
    LEFT JOIN TABLE3 T3 ON COALESCE(T2.REF,'DEF') = T3.CODE 
WHERE T1.CODE = XXXXX

Upvotes: 5

Related Questions