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

dnoeth 4628 posts Joined 11/04
02 Jun 2015

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  

 

Dieter

You must sign in to leave a comment.