CODEWITHSUNDEEP
sqlpostgresql
Steph74
Steph74

Reputation: 81

SQL query with AND?

I have a little problem writing a request. With the data example below, i want to recover the archive_id that have a document_type_id = 18 and 20

    +------------+------------------+
    | archive_id | document_type_id |
    +------------+------------------+
    |          1 |               20 |
    |          1 |               18 |
    |          3 |               20 |
    |          4 |               11 |
    |          2 |               23 |
    |          5 |               20 |
    |          6 |               23 |
    |          6 |               20 |
    |          6 |               18 |
    +------------+------------------+

Expected result :

    +------------+
    | archive_id |
    +------------+
    |          1 |
    |          6 |
    +------------+

Same question but with document_type_id = 18, 20 and 23 Expected result :

    +------------+
    | archive_id |
    +------------+
    |          6 |
    +------------+

Thank for your help

Upvotes: 0

Views: 61

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246403

For a general solution, you could for example use an array containing the archive_ids you are searching for:

WITH searched(ids) AS (VALUES (ARRAY[18,20,23]))
SELECT tab.archive_id
FROM tab CROSS JOIN searched
WHERE tab.document_type_id = ANY (searched.ids)
GROUP BY tab.archive_id
HAVING count(DISTINCT document_type_id) = cardinality(searched.ids);

Upvotes: 0

Ergest Basha
Ergest Basha

Reputation: 8973

A simple having count would do the trick.

First case

select archive_id
from your_table
where document_type_id in (18,20)
group by archive_id
having count(distinct document_type_id) =2;

https://dbfiddle.uk/MnjR_4a_

Second case

select archive_id
from your_table
where document_type_id in (18,20,23)
group by archive_id
having count(distinct document_type_id) =3;

https://dbfiddle.uk/v9m3nPiq

Upvotes: 4

Related Questions