Get all records that have ALL values passed as parameters-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

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'];

CodePudding user response：

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