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

Postgresql – Retrieve a line with different identifiers

I have a query writing problem. My table:

-> table users_keyword_document

 document_id | users_keyword_id
-------------+------------------
          65 |                1
          65 |                2
          66 |                1
          66 |                3

I want to retrieve only the document with document_id 65
The search can only be done with the user keyword identifiers so users_keyword_id

My query :

SELECT document_id, users_keyword_id
FROM users_keyword_document
WHERE users_keyword_id IN (1,2);

Returns the following result :

 document_id | users_keyword_id
-------------+------------------
          65 |                1
          65 |                2
          66 |                1

Is there an easy way to do this?
I use PostgreSQL.

Thanks for your help.

Tags:

3

Answers


  1. 0 
    SELECT document_id, users_keyword_id
FROM users_keyword_document
WHERE document_id = 65;
    Login or Signup to reply.
  2. 0

    Use HAVING as a condition that works on the group of records:

    SELECT document_id
    , ARRAY_AGG(users_keyword_id) AS keyword_ids
FROM users_keyword_document
GROUP BY document_id
HAVING ARRAY_AGG(users_keyword_id) <@ ARRAY[1, 2];
    Login or Signup to reply.
  3. 0

    You can use the GROUP BY and HAVING clause method. Can you try this?

    Select document_id
from users_keyword_document
where users_keyword_id in (1, 2)
group by document_id
having count(distinct users_keyword_id) = 2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search