CODEWITHSUNDEEP
sqlsql-server
JM1
JM1

Reputation: 1715

SQL How do you find missing ID's (not in sequence, or without sequence)?

I have data in a table that looks like the picture below. Each DocID, has a number of ItemID's associated with it. I am trying to find which DocID's don't have an ItemID of '14000' for example (bottom right of the picture.)

How can I get a list of all DocID's that don't have an ItemID of '14000' associated with it, please?

enter image description here

Here is one version of what I've tried so far.

SELECT      
d.DocID
FROM            tableD AS d
WHERE           d.GID = 19
            AND d.TID = 159
            AND 1283 NOT IN (SELECT d.ItemID FROM tableD)
GROUP BY    d.DocID

Here is a temp table in case it's helpful.

CREATE TABLE #t1 (
DocID   int
,GID    int
,TID    int
,ItemID     int
,Keyword VARCHAR(40)
)

INSERT INTO #t1 
VALUES 
(321654,    28, 1789,   13841,  'Jim'),
(321654,    28, 1789,   13851,  'Smith'),
(321654,    28, 1789,   13861,  'William'),
(321654,    28, 1789,   13871,  '000-00-0000'),
(321654,    28, 1789,   13881,  'SALARY'),
(978312,    28, 1789,   13841,  'Jim'),
(978312,    28, 1789,   13851,  'Smith'),
(978312,    28, 1789,   13861,  'William'),
(978312,    28, 1789,   13871,  '000-00-0000'),
(978312,    28, 1789,   13881,  'SALARY')

Thank you for your help.

Upvotes: 2

Views: 284

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175874

Using HAVING:

SELECT d.DocID   
FROM tableD AS d
WHERE  d.GID = 19 AND d.TID = 159
GROUP BY d.DocID
HAVING COUNT(CASE WHEN itemid = '14000' THEN 1 END) = 0

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270191

I am trying to find which DocID's don't have an ItemID of '14000' for example (bottom right of the picture.)

You can use aggregation with a having clause:

select docid
from tableD
group by docid
having sum(case when itemId = 14000 then 1 else 0 end) = 0;

This code counts the number of items that you care about. The having clause says none of those items are associated with a given docid.

I'm not sure what your code has to do with the question you asked.

Upvotes: 1

Related Questions