Find rows that doesn't have a certain value in a field


I need to generate a list of personID that does NOT have a record/row with attributeID 110. Please hlep.

SampleTable
personID | attributeID |  value
123 | 100 | 13
123 | 110 | 24
123 | 120 | 19
123 | 130 | 21
456 | 100 | 16
456 | 120 | 18
456 | 130 | 22


suma33Asked:
Who is Participating?
 
Patrick MatthewsCommented:
Select t1.personid
From sometable t1
Where not exists(
    Select t2.personid
    From sometable t2
    Where t2.personid = t1.personid and
        T2.attributeid = 110)
Group by t1.personid
0
 
Ephraim WangoyaCommented:
select *
from SampleTable
where attributeID <> 110
0
 
suma33Author Commented:
Although that would give me a list of rows that doesn't have attribute 110, it doesn't tell me which personIDs doesn't have a row with attribute 110.

I need to know which personIDs doesn't have a row with attribute 110 so I can insert that missing record.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ephraim WangoyaCommented:

select distinct personID
from SampleTable
where attributeID <> 110
0
 
ThomasianCommented:
SELECT personID
FROM SampleTable
GROUP BY personID
HAVING MIN(ABS(attributeID-110))>0

Open in new window

0
 
VipulKadiaCommented:
Try this one :

SELECT PersonID
FROM SampleTable
WHERE PersonID NOT IN (SELECT PersonID FROM SampleTable WHERE AttributeID=110)
0
 
BartVxCommented:
Another possibility (SQL 2005 and up):

 
SELECT DISTINCT personID
FROM SampleTable
EXCEPT
SELECT personID
FROM SampleTable WHERE attributeID = 110

Open in new window

0
 
suma33Author Commented:
Thank you. That worked and I could add the INSERT statement around it to do what I needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.