Need help to create SQL query

General

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?

1 REPLY
N/A

Re: Need help to create SQL query

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