CODEWITHSUNDEEP
sqlsql-server
ProfEng
ProfEng

Reputation: 33

IDs with multiple records - how to find the record I need?

I've got a table (Job Types) containing an ID (for the person completing the job), the year the job was completed (YYYY) and the Jobtype Id. 

|ID |   YEAR    | JOBTYPE|
|123A|  2017    |1
|124A|  2017    |2
|125A|  2016    | 2
|126A|  2017    |3
|123A|  2017    |2
|125A|  2016    |2

What I need is to find the IDs of everyone that completed only Jobtype 2 in 2017. In the above table, that would only be 124A. (125A completed Jobtype 2 in 2016; 123A did 1 and 2; etc.). Sidenote: it would not matter if an ID completed multiple Jobtype 2s in 2017 - I'd want those records.

How can I do this? I've tried a few NOT EXISTS queries but I think I'm getting stuck because 1 ID could potentially have a number of records against it. I'm guessing I need to somehow combine the data so every ID has one record, and then query it that way? Or am I missing something really obvious? I'm working in SQL Server 2012 and just cannot see a way forward. (Apologies if I'm missing something really simple, but I don't normally have cause to do anything much beyond select statements).

Upvotes: 1

Views: 233

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

You want information per ID, so GROUP BY ID. Then filter in HAVING. E.g.:

select id
from jobtypes
having min(year) = 2017
   and max(year) = 2017
   and min(jobtype) = 2
   and max(jobtype) = 2
;

The criteria can easily be adjusted. For instance others understood your criteria to mean: one or more 2017|2 records and no other record in 2017, whereas I understood it as one or more 2017|2 records and no other record at all. In case you want to look at 2017 only:

select id
from jobtypes
where year = 2017
having min(jobtype) = 2
   and max(jobtype) = 2
;

Whatever criteria to apply, the table has to be read just once.

Upvotes: 0

cloudsafe
cloudsafe

Reputation: 2504

SELECT a.*
FROM #test a
left join (Select id from #test where jobtype<>2 and [Year] = 2017) b on a.id=b.id
where jobtype=2 and year=2017 and b.id is null

Upvotes: 0

PP006
PP006

Reputation: 709

Hope this will work,

SELECT * FROM #Your_Table A WHERE JobType=2 AND Year=2017 AND NOT EXISTS(
SELECT ID FROM (SELECT *,Row_number() Over(Partition by ID Order by ID) AS Row_Num FROM #Your_Table)B
WHERE JobType=2 and Row_num>1 and B.ID=A.ID)

Upvotes: 0

JNevill
JNevill

Reputation: 50119

Write a query to get all of the ids where there were jobtype completions that are NOT 2 in 2017, then select only records where the id isn't in the results of that first query:

SELECT *
FROM table
WHERE jobtype = 2 and year = 2017
    AND id NOT IN (SELECT id FROM table WHERE jobtype <> 2 and Year=2017)

Upvotes: 4

Related Questions