Question 90469 in PostgreSQL Question posted in
The official documentation can be found here.

SQL query with AND? – Postgresql

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search