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

That's kind of what left join is for.

Luaan
– Luaan

2015-06-08 08:39:42 +00:00
Commented Jun 8, 2015 at 8:39 — Luaan
– Luaan, Commented Jun 8, 2015 at 8:39

Matteo Tassinari · Accepted Answer · 2015-06-08 10:38:27Z

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)

edited Jun 8, 2015 at 10:38

answered Jun 8, 2015 at 8:40

Matteo Tassinari

18.6k8 gold badges65 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Frank Schmitt Over a year ago

There's no IF in Oracle SQL.

Matteo Tassinari Over a year ago

Thanks, replaced with DECODE

Collectives™ on Stack Overflow

SQL Conditional Join on Columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related