Nested select

Database

Nested select

Hi Guys,

I have a table which includes Appl_id and Security_Type. There are four options under Security_Type i.e zero, 'A', 'B', 'Misc'. One appl_id can have zero security, 'A', 'B', 'Misc, 'A' & 'B' etcc... I need to get appl_id which have Security_Type one 'Misc' and one 'A' or' 'B'.  All apps having only Security_Type = 'B' or Security_Type ''A'  or Security_Type = 'A' & 'B' etc.. should be excluded. In the following example the output shod be Appl_id 6 & 7. showing their Security_type

 

Appl_id Security_Type

1   Misc

2   A

3   B

4   A

4   B

5   Zero

6   A

6   Misc

7   Misc

7   B

 

Regards,

Shoaib

2 REPLIES

Re: Nested select

If its going to be always 4 options, then below query can be used.

SEL * FROM

(SEL APPL_ID,

MAX(

CASE 

WHEN SECURITY_TYPE ='ZERO' THEN 'C'

WHEN SECURITY_TYPE ='MISC' THEN 'D' 

ELSE SECURITY_TYPE END

) AS MAX_S,

MIN(

CASE 

WHEN SECURITY_TYPE ='ZERO' THEN 'C'

WHEN SECURITY_TYPE ='MISC' THEN 'D' 

ELSE SECURITY_TYPE END

) AS MIN_S

FROM T2  GROUP BY 1

) A

WHERE A.MAX_S ='D'

AND A.MIN_S IN ('A','B')

Re: Nested select

Select APPL_ID from TBL where security_type ='MISC' and APPL_ID IN (Select APPL_ID from TBL where security_type in ('A','B') group by 1)

group by 1;

This should work.