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

Postgresql – Get all records that have ALL values passed as parameters

I have a table of properties:

+----+-----------------------------+
| prop_id | prop_name              |
+---------+------------------------+
| 1       | Cottage                |
+---------+------------------------+
| 2       | Mountain House         |
+---------+------------------------+
| 3       | Beach house            |
+---------+------------------------+

A table of accessories:

+----+-----------------------------+
| acc_id  | acc_name               |
+---------+------------------------+
| GAR     | With garden            |
+---------+------------------------+
| TER     | With terrace           |
+---------+------------------------+
| REN     | Recently renovated     |
+---------+------------------------+

A table that relates properties and accessories (properties2accessories):

+----+--------------+
| prop_id | acc_id  |
+---------+---------+
| 1       | GAR     |
+---------+---------+
| 1       | REN     |
+---------+---------+
| 2       | GAR     |
+---------+---------+
| 2       | REN     |
+---------+---------+
| 2       | TER     |
+---------+---------+
| 3       | GAR     |
+---------+---------+
| 3       | TER     |
+---------+---------+

I need all the properties that have ALL the accessories that I pass as parameters.

Correct examples:

a) Properties with "Garden" and "Recently renovated":

I should get props: 1, 2

b) Properties with "Garden" and "Terrace":

I should get props: 2, 3

I try:

SELECT *
FROM properties2accessories
WHERE acc_id IN ('GAR', 'REN');

but this get prop 3 too, that not has "Recently renovated"

I’m using Postgres 13

Any helps?

Tags:

3

Answers


  1. 0

    You con check with exists if another condition is present for a specific id

    SELECT
"prop_id"
FROM properties2accessories p 
WHERE ("acc_id" = 'GAR') 
AND EXISTS (SELECT 1 FROM properties2accessories WHERE "acc_id" = 'REN' AND  "prop_id" = p."prop_id")
    prop_id
    1
    2 
    SELECT 2

    fiddle

    Login or Signup to reply.
  2. 0

    You could do something like this:

    SELECT prop_id from (
 select prop_id, array_agg(acc_id) acc_array
 FROM properties2accessories
 group by prop_id) d
WHERE array['GAR', 'REN'] <@ acc_array;
    Login or Signup to reply.
  3. 0

    That’s what HAVING can do for you, it’s a WHERE condition over the entire group:

    SELECT  prop_id
FROM    properties2accessories
WHERE   acc_id IN ('GAR', 'REN')
GROUP BY prop_id
HAVING  ARRAY_AGG(acc_id) @> ARRAY['GAR', 'REN'];
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search