All Forums Database
shoaibrafiq 5 posts Joined 04/15
01 Jun 2015
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

yuvaevergreen 93 posts Joined 07/09
02 Jun 2015

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')

manib0907 61 posts Joined 04/15
05 Jun 2015

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.

Cheers,
Mani

You must sign in to leave a comment.