All Forums General
praveen.k.0290 5 posts Joined 11/13
22 Feb 2015
Need help to create SQL query

Hi,

 

I have a situation like below.

I have two tables(table1 and table2). For each col1 value, I need to pick the value for col2 based on below condition(joining column is col1):

 

If all the col3 value from table 2 present in col3 value from table 1 then pick col2 value from table2 else pick col2 value from table1.

 

Eg:

 

For value 10 from col1, A1 has 2,3,4,6 and P1 has 2,3,4 then I need to pick P1.

i.e. A1 -- 2,3,4,6

P1 -- 2,3,4 output: P1

For value 11 from col1, A2 has 2,4 and P2 has 2,3 then I need to pick A2

i.e. A2 -- 2,4

P2 -- 2,3 output: A2

 

Table 1

 

Col1    col2    col3

10       A1      2

10       A1      3

10       A1      4

10       A1      6

 

11       A2      2

11       A2      4

 

12       A3      2

12       A3      4

 

13       A4      2

13       A4      3

 

 

Table 2

 

Col1   col2   col3

10      P1      2

10      P1      3

10      P1      4

 

11      P2      2

11      P2      3

 

12      P3      2

12      P3      4

 

13      P4      2

13      P4      3

13      P4      5

 

 

Output

 

Col1     col2

10        P1

11        A2

12        P3

13        A4

 

Can anyone help me on this to write SQL for above condition?

dnoeth 4628 posts Joined 11/04
23 Feb 2015

I didn't fully understand what you want, but this might work:

select t2.col1,
  -- check if any NULLs where returned
  case when count(*) = count(t1.col1) then t2.col2 else t1.col2 end
from t2 left join t1 -- non matching values return NULLs in t1.col1
on t1.col1 = t2.col1
and t1.col3 = t2.col3
group by 1

 

Dieter

You must sign in to leave a comment.