CODEWITHSUNDEEP
sqlsql-servert-sql
shinya
shinya

Reputation: 403

T-SQL Query - How to not include some results from different row

I have a data something like below.

ID | Value 
------------    
 1 | A 
 1 | B 
 1 | C 
 2 | A 
 2 | C
 3 | C

I'm trying to get ID which has A but not including B.

In this case, I should only get ID 2 as a result.

If I query like

SELECT DISTINCT ID 
FROM TABLE 
WHERE (VALUE = 'A' AND VALUE <> 'B')

I get 1 and 2 as a result.

Is this even possible from this database structure?

Upvotes: 1

Views: 96

Answers (4)

David Faber
David Faber

Reputation: 12485

There is also the possibility of using WHERE NOT EXISTS:

SELECT t1.id FROM table t1
 WHERE t1.value = 'A'
   AND NOT EXISTS ( SELECT 1 FROM table t2
                     WHERE t2.id = t1.id
                       AND t2.value = 'B' );

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

Even though you accepted an answer, I'll write another variant. I think it is easier to write and understand. In terms of performance you have to check how different variants work on your system with your real data.

DECLARE @T TABLE (ID int, Value char(1));

INSERT INTO @T (ID, Value) VALUES (1, 'A');
INSERT INTO @T (ID, Value) VALUES (1, 'B');
INSERT INTO @T (ID, Value) VALUES (1, 'C');
INSERT INTO @T (ID, Value) VALUES (2, 'A');
INSERT INTO @T (ID, Value) VALUES (2, 'C');
INSERT INTO @T (ID, Value) VALUES (3, 'C');

SELECT ID FROM @T WHERE Value = 'A'

EXCEPT

SELECT ID FROM @T WHERE Value = 'B'

Result set:

ID
2

Upvotes: 1

void
void

Reputation: 7890

here is another query for it:

select distinct tb.ID from tbl_name tb
where tb.Value='A' and tb.ID not in(select distinct tbl.ID from tbl_name tbl
where tbl.Value='B')

Upvotes: 0

G B
G B

Reputation: 1462

You need to exclude any ID's that contain the value 'B'. you can do this with a left join and a check for null... i.e. no match.

select d.*
from data d
    left join (select id from data where value = 'B') x --exclude these
        on d.id = x.id
where x.id is null
    and d.value = 'A'

Upvotes: 2

Related Questions