How to get Key based on type and sub type

Question

How to get Key based on type and sub type

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
September 14, 2022 at 7:27 pm

#4087799

I Have sample data that looks like below. We have similar data like below for ton's of different keys. for each key data will be like 5 to 6 rows based on type and sub type ; I need to get the Key from the below records where the data set contain type COLL and Subtype MOD but does not exists case type QUAT and sub type BALANCE.
For each type COLL and SUb type MOD, there should be associated row that is type QUAT and SUbtype BALANCE. If you see the below set does'nt has that. I am trying to pull all the keys from the database where for each key it does not exists case type QUAT but exists case type COLL.

id key type subtype
1 100 INCOM RWQ
2 100 BALL BAS_AMT
3 100 COLL MOD
4 100 POC STAND

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101093 More actions · Answer 1

SELECT key
FROM dbo.your_table_name_here
GROUP BY key
HAVING MAX(CASE WHEN type = 'COLL' AND subtype = 'MOD' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN type = 'QUAT' AND subtype = 'BALANCE' THEN 1 ELSE 0 END) = 0
ORDER BY key --optional, of course

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 2

Thank you Scott.

Suppose If i want to check type COLL and SUb type MOD, with associated row that has type QUAT and SUbtype BALANCE. If you see the below set this time it has that. I am trying to pull all the keys from the database where for each key it exists case type QUAT as well as type COLL.

Do i need to change having clause little bit?

id key type subtype

1 100 INCOM RWQ

2 100 BALL BAS_AMT

3 100 COLL MOD

4 100 POC STAND

5 100 QUAT BALANCE

ScottPletcher SSC Guru Points: 101093 More actions · Answer 3

Yes. If you only want a key that has both conditions, change the MAX:

MAX(CASE WHEN type = 'QUAT' AND subtype = 'BALANCE' THEN 1 ELSE 0 END) = 1

Note that you can get any combination of found values -- or missing values -- using a combination of MAX conditions. That's one reason I did it that way; also, it is a very efficient method.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

aaron.reese SSChampion Points: 13498 More actions · Answer 4

You might find it easier to reason about with EXCEPT and INTERCECT.

SELECT DISTINCT KEY FROM myTable WHERE type = 'COLL' and subtype = 'MOD'

EXCEPT

SELECT DISTINCT KEY FROM myTable WHERE type = 'QUAT' and subtype = 'BALANCE'

Will get all the COL/MOD that don't have a QUAT/BALANCE

SELECT DISTINCT KEY FROM myTable WHERE type = 'COLL' and subtype = 'MOD'

INTERSECT

SELECT DISTINCT KEY FROM myTable WHERE type = 'QUAT' and subtype = 'BALANCE'

gets keys that have both.

ScottPletcher SSC Guru Points: 101093 More actions · Answer 5

aaron.reese wrote:

You might find it easier to reason about with EXCEPT and INTERCECT.
SELECT DISTINCT KEY FROM myTable WHERE type = 'COLL' and subtype = 'MOD'

EXCEPT

SELECT DISTINCT KEY FROM myTable WHERE type = 'QUAT' and subtype = 'BALANCE'
Will get all the COL/MOD that don't have a QUAT/BALANCE
SELECT DISTINCT KEY FROM myTable WHERE type = 'COLL' and subtype = 'MOD'

INTERSECT

SELECT DISTINCT KEY FROM myTable WHERE type = 'QUAT' and subtype = 'BALANCE'
gets keys that have both.

The big disadvantage of this method is that it requires two full passes of the table rather than one.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".