CODEWITHSUNDEEP
mysql
dasj19
dasj19

Reputation: 186

SQL - how to select rows that do not contain a certain ID

So I have the table flag_counts with the following columns:

flag_id content_id
1       10
2       10
2       11

and the following query

SELECT flag_id, content_id FROM flag_counts
WHERE flag_id != 1

and of course it returns

flag_id content_id
2       10
2       11

How can I change my query so that it returns all content_ids that were not flagged with "1". In this example, I would like the query to return only the last row of the table since the content_id "10" was flagged once with "1".

Upvotes: 1

Views: 5954

Answers (4)

Adarsh
Adarsh

Reputation: 726

One of the following method can be used to solve this.

SELECT  DISTINCT a.content_id 
FROM 
    flag_counts a 
LEFT JOIN flag_counts b
ON  (a.content_id = b.content_id AND b.flag_id = 1)
WHERE   b.content_id IS NULL;

SELECT  DISTINCT a.content_id FROM flag_counts a  
WHERE NOT EXISTS (SELECT 1 FROM flag_counts b WHERE a.content_id = b.content_id AND b.flag_id = 1)

Upvotes: 0

Andrey
Andrey

Reputation: 60055

SELECT whatever
FROM flag_counts
WHERE content_id NOT IN (
   SELECT content_id
   FROM flag_counts
   WHERE flag_id = 1
)

or via JOIN

SELECT DISTINCT c1.*
FROM flag_counts c1
LEFT JOIN flag_counts c2
 ON c1.content_id = c2.content_id and c2.flag_id = 1
WHERE c2.content_id IS NULL;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269453

This addresses the question: How to change my query so that it returns all content_ids that were not flagged with "1"? The "clarification" is actually more confusing, because SQL tables represent unordered sets, so there is no "last" row.

If you have a separate table of content ids, then the fastest approach might be:

select c.*
from contents c
where not exists (select 1
                  from flag_count fc
                  where fc.content_id = c.content_id and flag_id = 1
                 );

For this, you would want an index on flag_count(content_id, flag_id).

If you use flag_count instead of contents, then you might get duplicates.

Upvotes: 1

juergen d
juergen d

Reputation: 204746

Group by the content_id and take only those having zero records with flag_id = 1

SELECT content_id
FROM flag_count
GROUP BY content_id
HAVING sum(flag_id = 1) = 0

Upvotes: 3

Related Questions