CODEWITHSUNDEEP
sqloracle-databaseoracle9i
HJW
HJW

Reputation: 23443

Oracle SQL: Joining another table with one missing tuple

I have the following two tables, one stores order information, one stores key|value information.

There is no CD = D for key COLOR_CD. I would like to join both tables to get all orders irregardless of the key|value pair not found in the MASS_DECODE table.

Can i get some help please :D

ORDER_INFORMATION

ORDER_NUMBER    |COLOR_CD   |
----------------|-----------|
1               |A          |
2               |B          |
3               |C          |
4               |D          |

MASS_DECODE

KEY         |CD             |VALUE      |
------------|---------------|-----------|
COLOR_CD    |A              |Green      |
COLOR_CD    |B              |Blue       |
COLOR_CD    |C              |Red        |
SIZE_CD     |A              |Large      |
SIZE_CD     |B              |Medium     |
SIZE_CD     |C              |Small      |

SQL:

select order_number, cd, value
from order_information
left outer join mass_decode 
on (color_cd = cd)
and key = 'COLOR_CD';

Outcome:

ORDER_NUMBER    |CD         |VALUE      |
----------------|-----------|-----------|
1               |A          |Green      |
2               |B          |Blue       |
3               |C          |Red        |

Expected:

ORDER_NUMBER    |CD         |VALUE      |
----------------|-----------|-----------|
1               |A          |Green      |
2               |B          |Blue       |
3               |C          |Red        |
4               |D          |NULL       |

EDIT: I am sorry i have presented incorrect information for my tables. Since been corrected.

Upvotes: 0

Views: 318

Answers (3)

Kirill Leontev
Kirill Leontev

Reputation: 10941

select *
  from order_information oi
   left join mass_decode md 
     on (
            oi.color_cd = md.cd 
        and oi.key = md.key
     )
where oi.key = 'KEY_A';

SQLFiddle

upd:

According to your updates:

select *
  from order_information oi
   left join mass_decode md 
     on oi.color_cd = md.cd
where md.key = 'COLOR_CD' or md.key is null;

SQLFiddle

Upvotes: 1

billy
billy

Reputation: 1160

This should do : 

SELECT 
    order_number, 
    color_cd AS cd, 
    (
        SELECT value FROM mass_decode m2 WHERE m2.key = o.key AND m2.cd = o.color_cd
    ) AS value
FROM order_information o
WHERE o.key =  'KEY_A'

Upvotes: 0

eaolson
eaolson

Reputation: 15094

Your column names are ambiguous. You have two columns named the same in two tables, so which column does the key in your where clause refer to? I think in later versions of Oracle, this will actually give you an error. Try this:

select oi.order_number, oi.color_cd, md.value
from order_infomation oi
left outer join mass_decode md
on (oi.color_cd = md.cd)
where oi.key = 'KEY_A';

Edit:

Based on your response to @billy, it sounds like that wasn't working for you. You could also try putting the key predicate into the join clause:

select oi.order_number, oi.color_cd, md.value
from order_infomation oi
left outer join mass_decode md
on (oi.color_cd = md.cd) and oi.key = 'KEY_A';

Upvotes: 1

Related Questions