CODEWITHSUNDEEP
mysqlsqlsql-serverselect
tkja
tkja

Reputation: 2030

Select values in SQL that have a colum value in one row and not a specific other column value in another

From the following table, I would like to return the set of all books that have topic 3 but do not have topic 4.

id  book    topic
10  1000    3
11  1000    4
12  1001    2
13  1001    3
14  1002    4
15  1003    3

The correct table should be:

book    
1001
1003

I made a SQL Fiddle with this for testing here.

So far, I tried the following, but it returns 1000, 1001, 1003 (because I am not comparing two rows with each other, and do not know how to do):

SELECT DISTINCT a.id
FROM TOPICS a, TOPICS b
WHERE a.id = b.id
AND a.topic = 3 

AND NOT EXISTS (

SELECT DISTINCT c.id
FROM TOPICS c
WHERE a.id = c.id
AND b.topic = 4

)

Upvotes: 1

Views: 47

Answers (3)

Mureinik
Mureinik

Reputation: 311563

You're almost there. The not exists condition is definitely the right idea, you just need to apply another one of these for checking the existence of topic 3:

SELECT DISTINCT a.book
FROM   topics a
WHERE  EXISTS (SELECT *
               FROM   topics b
               WHERE  a.book = b.book AND b.topic = 3) AND
      NOT EXISTS (SELECT *
                  FROM   topics b
                  WHERE  a.book = b.book AND b.topic = 4)

Upvotes: 2

jpw
jpw

Reputation: 44891

Since you're using SQL Server (at least in the fiddle) you could use the exceptset operator:

SELECT DISTINCT book FROM TOPICS WHERE topic = 3 
EXCEPT
SELECT DISTINCT book FROM TOPICS WHERE topic = 4

This would return 1001 and 1003.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175826

Using NOT EXISTS:

SELECT a.book
FROM TOPICS a
WHERE a.topic = 3 
   AND NOT EXISTS
   (SELECT 1
    FROM TOPICS b
    WHERE a.book = b.book
    AND b.topic = 4)

Upvotes: 1

Related Questions