query to get records with exact values of a field

Question

I have a mysql table like this:

id_doc | id_category
1        a
1        c
1        f
2        a
2        g
3        a
3        b
3        f
4        h

I need a query to extract docs that have categories "a" and "f".

example:

id_doc
1
3

please help me! Thanks

Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. For more details, [read this.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) — Zohar Peled, Jan 13 '19 at 16:14

score 1 · Accepted Answer · answered Jan 13 '19 at 16:23

Aggregate and use HAVING to check if the number of distinct groups a document is in equals 2.

SELECT id_doc
       FROM document_category
       WHERE id_category IN ('a',
                             'f')
       GROUP BY id_doc
       HAVING count(DISTINCT id_category) = 2;

Or, if you have a documents table use two EXISTS.

SELECT d.id_doc
       FROM document d
       WHERE EXISTS (SELECT *
                            FROM document_category dc
                            WHERE dc.id_doc = d.id_doc
                                  AND dc.id_category = 'a')
             AND EXISTS (SELECT *
                                FROM document_category dc
                                WHERE dc.id_doc = d.id_doc
                                      AND dc.id_category = 'f');

Exactly, it works ! Thank you so much – Antonio-Dev Jan 13 '19 at 16:27 — Antonio-Dev, Jan 13 '19 at 16:27

query to get records with exact values of a field

1 Answers1