Need help to get the SQL logic

Database
Enthusiast

Need help to get the SQL logic

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?

2 REPLIES
Enthusiast

Re: Need help to get the SQL logic

Check this...

Select

T1.Col1,  

CASE

        WHEN ACTCNT = MATCNT

            Then T2.Col5

        else T1.Col2

End

from

(

Select col1 as C1,count(*) as ACTCNT

from SRX_WRK_Test1

group by 1

)Q1

,

(Select A.Col1 as C1, count(*) as MATCNT from

SRX_WRK_Test1 A,

SRX_WRK_Test2 B

Where A.Col1 =B.col4

and A.col3=B.col6

group by 1)Q2

,SRX_WRK_Test1 T1

,SRX_WRK_Test2 T2

Where Q1.C1=Q2.C1

and T1.Col1 =T2.col4

and T1.Col1=Q1.C1

group by 1,2;

N/A

Re: Need help to get the SQL logic

Hi,

Practically the same as above aarsh.dave code, May be a bit simpler

SEL

ACTCNT.COL1,

CASE WHEN ACTCNT.ACT=MATCHCNT.MNT

THEN ACTCNT.COL2

ELSE MATCHCNT.COL2

END AS COL2

FROM

(

SEL B.COL1 AS COL1,A.COL2 AS COL2,COUNT(1) AS MNT

FROM

TESTTABLE1 A

INNER JOIN

TESTTABLE2 B

ON A.COL1=B.COL1

AND A.COL3=B.COL3

GROUP BY 1,2

 ) MATCHCNT

 INNER JOIN  

 (

 SEL A.COL1 AS COL1,A.COL2 AS COL2,COUNT(1) AS ACT

 FROM

TESTTABLE2 A

 GROUP BY 1,2

 ) ACTCNT

 ON MATCHCNT.COL1=ACTCNT.COL1

 ORDER BY 1,2;