CODEWITHSUNDEEP
sqlpostgresqlselectionmultiple-value
peakingcube
peakingcube

Reputation: 1418

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

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.

Upvotes: 2

Views: 1366

Answers (3)

Hamidreza
Hamidreza

Reputation: 3128

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

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

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;

Upvotes: 3

Mureinik
Mureinik

Reputation: 311998

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)

Upvotes: 2

Related Questions