SQL Conditional Join on Columns

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 


Source: oracle

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.