CODEWITHSUNDEEP
sqlsql-serversql-server-2008
shadowjfaith
shadowjfaith

Reputation: 942

Select single row based on multiple criteria in same column

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.

Upvotes: 1

Views: 1014

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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

Upvotes: 3

SlimsGhost
SlimsGhost

Reputation: 2909

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

Upvotes: 2

Related Questions