SQL - How to select records with value A but not B? (A and B belongs to different rows)

Question

Suppose I have a table like below:

row_id  record_id   tag_id
1       1           2
2       1           3
3       2           2
4       2           4
5       3           2
6       3           3

I want to get those record_id which they have record with tag_id of value 2 but does not have 3, in this case, I want to get record_id 2. I can only think of a SQL statement with 3 selection but it seems bulky. Is there any simpler, faster way to achieve this? Thanks.

Edit:

The SQL I got:

SELECT record_id
FROM table_A 
WHERE record_id NOT IN (SELECT record_id 
                         FROM table_A 
                         WHERE record_id IN (SELECT record_id 
                                              FROM table_A 
                                              WHERE tag_id = 2) 
                         AND tag_id =3) 
AND record_id IN (SELECT record_id FROM table_A WHERE tag_id = 2) GROUP BY record_id

And each record_id may have 1 to any number of tag_id value.

Welcome to Stack Overflow. Please read [Stack Overflow: How to ask](http://stackoverflow.com/questions/how-to-ask) and [Jon Skeet's Question Checklist](http://msmvps.com/blogs/jon_skeet/archive/2012/11/24/stack-overflow-question-checklist.aspx) to find out how to ask a good question that will generate good useful, answers. — Our Man in Bananas, Jul 08 '14 at 09:25

Hamidreza · Answer 1 · 2014-07-08T10:37:15.253

3

You can use bool_or() function in your query in postgresql:

select record_id from table1 group by record_id
having bool_or(tag_id = 2) and not bool_or(tag_id = 3);

SQL Fiddle

edited Jul 08 '14 at 10:37

answered Jul 08 '14 at 09:57

Hamidreza

3,038
1
18
15

1

Can be done without a subquery using `having bool_or(tag_id = 2) and not bool_or(tag_id = 3)`. BTW `tag_2 is true` is same as `tag_2`. `tag_3 is false` is same as `not tag_3` – Ihor Romanchenko Jul 08 '14 at 10:32

score 3 · Accepted Answer · answered Jul 08 '14 at 10:22

3

This can be simply written as

SELECT record_id FROM table_A WHERE tag_id = 2
EXCEPT
SELECT record_id FROM table_A WHERE tag_id = 3;

answered Jul 08 '14 at 10:22

Thorsten Kettner

89,309
7
49
73

score 2 · Answer 3 · answered Jul 08 '14 at 09:32

One way of achieving this is by using the IN operator with a subquery:

SELECT *
FROM   my_table
WHERE  tag_id = 2 AND record_id NOT IN (SELECT record_id
                                        FROM   my_table
                                        WHERE  tag_id = 3)

A similar solution could also be achieved with the EXISTS operator:

SELECT *
FROM   my_table a
WHERE  tag_id = 2 AND NOT EXISTS (SELECT record_id
                                  FROM   my_table b
                                  WHERE  tag_id = 3 AND
                                         a.record_id = b.record_id)

SQL - How to select records with value A but not B? (A and B belongs to different rows)

3 Answers3

SQL Fiddle