SQL Conditional Join on Columns

Question

There are three tables:

Clearance:  CLEARANCE_ID, ZONE, LOC
Zone:       ZONE, ZONE_DESC
Location:   LOC, LOC_DESC

I have the following query.

Select * from RC.CLEARANCE_ID, RC.TYPE, RC.ZONE, R.ZONE_DESC, RC.LOC, L.LOC_DESC
FROM CLEARANCE RC, ZONE R, LOC L

The value of RC.TYPE is 0 for zone, 1 for location.

I want ZONE_DESC (from zone table) to appear if the RC.TYPE = 0 and LOC_DESC to appear from LOC table if the RC.TYPE = 1

If I join CLEARANCE table with both the other tables, if doesn't bring records as one of the fields (ZONE or LOC) is null in every case.

How to I do this query?

Eg:

CLEARANCE ID  TYPE  ZONE  ZONE_DESC  LOC   LOC_DESC
   3           0     10    ZONE1     null     null
   4           1    null   null       50    Location1 

Answer 1

This should do it:

SELECT c.clearance_id
    , c.type
    , DECODE(c.type, 0, z.zone, 1, l.loc) zone_loc
    , DECODE(c.type, 0, z.zone_desc, 1, l.loc_desc) zone_loc_desc
FROM clearance c
LEFT JOIN zone z ON (c.type = 0 AND c.zone = z.zone)
LEFT JOIN location l ON (c.type = 1 AND c.loc = l.loc)