SQL query with AND?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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);