• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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


0
suma33
Asked:
suma33
1 Solution
 
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
 
Ephraim WangoyaCommented:

select distinct personID
from SampleTable
where attributeID <> 110
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now