oracle database merge results from multiple tables

Question

I want to merge data coming from two tables :-

table T1 (id,c2,c3,switch)

table T2 (id,d2,d3 )

T1
-----
id   c1      c2      switch
1   joe    darling     Y
1   maria    kk        N

T2
--------------
id   d1       d2
1  sydney   austraila

now if the switch in T1 is 'Y'

i want the output as

joe darling sydney australia // which is fine..

and if switch is 'N'

i still want the first and last name based on switch which is 'Y' and rest of the values from T2 table.

joe darling sydney australia //how to achieve this.

Upvotes: 2

Views: 104

Answer 1

Suppose you've got only one Y switch per ID then try this:

SELECT 
     T12.C1, T12.C2, T2.d1, T2.D2
FROM T1
JOIN T1 as T12 ON (T1.ID=T12.ID) AND (T12.switch='Y')    
LEFT JOIN T2 on (T1.ID=T2.ID)

SQLFiddle demo