SQL Sub Queries

General
Enthusiast

SQL Sub Queries

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 at least one 'Misc' and at least one 'A' or' 'B'.  All apps haveing only Security_Type = 'B' or Security_Type ''A'  or Security_Type = 'A' & 'B' etc.. should be excluded. I should only get 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

1 REPLY
Junior Contributor

Re: SQL Sub Queries

Hi Shoaib,

this is usually done using HAVING plus CASEes:

SELECT Appl_id
FROM tab
GROUP BY Appl_id
HAVING SUM(CASE WHEN Security_Type = 'Misc' THEN 1 ELSE 0 end) > 0
AND SUM(CASE WHEN Security_Type IN ('A', 'B') THEN 1 ELSE 0 end) > 0

SELECT Appl_id
,MAX(CASE WHEN Security_Type = 'A' THEN Security_Type END) AS Security_Type_A
,MAX(CASE WHEN Security_Type = 'B' THEN Security_Type END) AS Security_Type_B
,MAX(CASE WHEN Security_Type = 'Misc' THEN Security_Type END) AS Security_Type_Misc
FROM tab
GROUP BY Appl_id
HAVING Security_Type_Misc IS NOT NULL
AND (Security_Type_A IS NOT NULL OR Security_Type_B IS NOT NULL)

If you need to show more columns you might switch to a Group Sum instead:

SELECT *
FROM tab
QUALIFY SUM(CASE WHEN Security_Type = 'Misc' THEN 1 ELSE 0 end)
OVER (PARTITION BY Appl_id) > 0
AND SUM(CASE WHEN Security_Type IN ('A', 'B') THEN 1 ELSE 0 end)
OVER (PARTITION BY Appl_id) > 0