CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Kirill
Kirill

Reputation: 77

TSQL - Select rows with same column A but different column B

I'm trying to find rows (Name) that does not have ID = 1. For example, if my table looked like this:

Name     ID        
--------------
A      1
A      0
B      1
B      0
C      0 
D      2
D      0

The answer to this query would be:

Name
-----
C
D

Do you have any idea?

Upvotes: 1

Views: 420

Answers (4)

user8744526
user8744526

Reputation: 1

Select Distinct name 
From myTable
Where name not in (Select name From myTable Where id= 1)

Upvotes: 0

Kishan Javiya
Kishan Javiya

Reputation: 13

Try this query:

SELECT DISTINCT(name)
FROM tbl t1 
WHERE 
NOT EXISTS (SELECT name FROM tbl t2 WHERE ID=1 AND t1.name=t2.name)

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82524

Here is one way to do it:

SELECT DISTINCT Name
FROM Table t0
WHERE NOT EXISTS
(
    SELECT 1
    FROM Table t1
    WHERE t0.Name = t1.Name
    AND t1.Id = 1
)

Upvotes: 1

gotqn
gotqn

Reputation: 43666

SELECT Name
FROM myTable
GROUP BY Name
HAVING SUM(CASE WHEN ID = 1 THEN 1 ELSE 0 END) = 0

Upvotes: 3

Related Questions