Select single row based on multiple criteria in same column

Question

It's very difficult to describe what I want with words so here are the tables I'm dealing with

Persons
First | Last  | ID
John  | Smith |  1
Jane  | Smith |  2

PerKey
ID |  KW  | HITS
1  | P.E. |  1
1  | M E  |  1
1  | HVAC |  4
2  | Acct |  7

What I want to do is select from persons where criteria meets multiple rows.

For instance I would like to know who has a 'P.E.' AND 'M E' AND 'HVAC'.

In this case that would return John Smith.

I have been able to accomplish this with the IN and HAVING clauses, but that limits me to not being able to filter how many times an individual has that KW and OR clauses.

Say I only wanted someone with 5 hits on 'HVAC', that would rule John Smith out ideally. Here's the query I have atm:

 SELECT first, last 
 FROM persons 
 LEFT JOIN perkey ON persons.id = perkey.id
 WHERE kw IN ('P.E.','M E','HVAC')
 GROUP BY first, last, persons.id
 HAVING COUNT(DISTINCT kw) = 3

I'm using SQL Server 2008. I cannot change the data structure either as it's proprietary. Thanks for any help/advice.

Each 'id' has a single row for each 'kw' and the 'hits' column is what I want to filter off of. i.e. I want someone with HVAC 'kw' and >5 in the 'hits' column. Does that make sense? — shadowjfaith
– shadowjfaith, Commented Aug 23, 2016 at 16:25

Giorgos Betsos · Accepted Answer · 2016-08-23 18:10:44Z

3

You can use conditional aggregation in order to selectively count each of the possible kw values:

 SELECT first, last,
        COUNT(CASE WHEN kw = 'P.E.' THEN 1 END) AS PE,
        COUNT(CASE WHEN kw = 'M E' THEN 1 END) AS ME,
        COUNT(CASE WHEN kw = 'HVAC' THEN 1 END) AS HVAC
 FROM persons 
 LEFT JOIN perkey ON persons.id = perkey.id
 WHERE kw IN ('P.E.','M E','HVAC')
 GROUP BY first, last, persons.id
 HAVING COUNT(DISTINCT kw) = 3

Edit:

If you want to filter records based on, say, HVAC occurrences, then you can add the conditional aggregate in HAVING clause, e.g.:

 SELECT first, last            
 FROM persons 
 LEFT JOIN perkey ON persons.id = perkey.id
 WHERE kw IN ('P.E.','M E','HVAC')
 GROUP BY first, last, persons.id
 HAVING COUNT(DISTINCT kw) = 3 AND 
        COUNT(CASE WHEN kw = 'HVAC' THEN 1 END) > 5

Edit2:

If you want to filter records based on the value of hits field, then you can use:

 SELECT first, last            
 FROM persons 
 LEFT JOIN perkey ON persons.id = perkey.id
 WHERE kw IN ('P.E.','M E','HVAC')
 GROUP BY first, last, persons.id
 HAVING COUNT(DISTINCT kw) = 3 AND 
        COUNT(CASE WHEN hits > 4 THEN 1 END) > 0

edited Aug 23, 2016 at 18:10

answered Aug 23, 2016 at 15:19

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Radu Gheorghiu Over a year ago

Syntax is incorrect, can you switch the GROUP BY and HAVING clauses?

Giorgos Betsos Over a year ago

@RaduGheorghiu Thanks for the remark. I just copy/pasted the query from the OP.

shadowjfaith Over a year ago

This is counting occurrences as in if that 'id' and 'kw' was repeated several times in the table (correct me if I'm wrong); however, the table is not set up that way. Each 'id' has a single row for each 'kw' and the 'hits' column is what I want to filter off of. i.e. I want someone with HVAC 'kw' and >5 in the 'hits' column. Does that make sense?

SlimsGhost · Accepted Answer · 2016-08-23 15:27:36Z

2

Conditional grouping should work, both to check for all types of kw, and to limit or further inspect the # of occurrences of any/all types. Like this:

SELECT persons.id, first, last,
    SUM(CASE WHEN kw = 'P.E.' THEN 1 ELSE 0 END) AS PE,
    SUM(CASE WHEN kw = 'M E' THEN 1 ELSE 0 END) AS ME,
    SUM(CASE WHEN kw = 'HVAC' THEN 1 ELSE 0 END) AS HVAC
FROM persons 
LEFT JOIN perkey ON persons.id = perkey.id
WHERE kw IN ('P.E.','M E','HVAC')
GROUP BY first, last, persons.id
HAVING SUM(CASE WHEN kw = 'P.E.' THEN 1 ELSE 0 END) > 0 
AND SUM(CASE WHEN kw = 'M E' THEN 1 ELSE 0 END) > 0 
AND SUM(CASE WHEN kw = 'HVAC' THEN 1 ELSE 0 END) > 0

answered Aug 23, 2016 at 15:27

SlimsGhost

2,9091 gold badge12 silver badges16 bronze badges

2 Comments

Matt Over a year ago

HVAC conditional aggregation should be = 5 based on OP's statement "Say I only wanted someone with 5 hits on 'HVAC'"

SlimsGhost Over a year ago

Yes, but that was just an example ("say i wanted...") of what I took to be the general case. It's not a big stretch to see that it is applicable for that specific case.

Collectives™ on Stack Overflow

Select single row based on multiple criteria in same column

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related