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

user_3729714 0
Viewed 120

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.

sql-server sql
2 Answers

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
user_50708790

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.

user_11440350
Related