CODEWITHSUNDEEP
sqlsql-servert-sql
Mike Petri
Mike Petri

Reputation: 605

TSQL - exclude users who who meet ONLY criteria

The goal is to exclude, or otherwise flag the customers who ONLY have records of ItemNumber like '9%'.

    CustomerID  ItemNumber
    85610           99
    85611           11
    85611           99
    85612           13
    85612           11
    85612           98
    85613           98
    85613           99

In this example, CustomerIDs 86510 and 86513 should be excluded, or flagged, as the ONLY ItemNumbers associated with their records are like '9%'.

I would appreciate any assistance.

Upvotes: 2

Views: 469

Answers (5)

anon
anon

Reputation: 

SELECT CustomerID, ItemNumber
FROM dbo.table AS t
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.table 
    WHERE CustomerID = t.CustomerID 
    AND ItemNumber NOT LIKE '9%'
);

Upvotes: 6

AnandPhadke
AnandPhadke

Reputation: 13496

create table tc(CustomerID int,ItemNumber varchar(10))

INSERT INTO tc
VALUES(85610,99),
    (85611,11),
    (85611,99),
    (85612,13),
    (85612,11),
    (85612,98),
    (85613,98),
    (85613,99)

select CustomerID from tc 
where ItemNumber like '9%'
group by CustomerID 

except

select CustomerID from tc 
where ItemNumber not like '9%'
group by CustomerID

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453287

Another option

SELECT CustomerID
FROM YourTable
GROUP BY CustomerID
HAVING MAX(CASE WHEN ItemNumber LIKE '9%' THEN 1 END) = 1
    AND MAX(CASE WHEN ItemNumber NOT LIKE '9%' THEN 1 END) IS NULL

Upvotes: 2

Johnny
Johnny

Reputation: 1143

Unless I'm missing something, why not just include a WHERE clause that has ItemNumber NOT LIKE '9%'

Upvotes: -1

Aushin
Aushin

Reputation: 1208

SELECT 
    CustomerID
FROM Table T
WHERE NOT EXISTS (SELECT CustomerID FROM Table WHERE CustomerId = T.CustomerId AND ItemNumber NOT LIKE '9%')

Upvotes: 2

Related Questions