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.

You have to have something to link thoose tables and then do a simple join. It can't be done because you don't have any link between then (based on the data.) — Jorge Campos
– Jorge Campos, Commented Jan 15, 2014 at 13:05

valex · Accepted Answer · 2014-01-15 13:16:53Z

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)

answered Jan 15, 2014 at 13:09

valex

24.2k7 gold badges47 silver badges62 bronze badges

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

1 Answer 1