I have three tables growth_spaces, equipment and growth_space_equipment which links the two tables together. I'm trying to write a query to get all growth spaces that have all of the equipment that I give as an array. For example if the array were ('Mist Propagation, Automated Curtain) I only want growth spaces that have both of these equipment (not ones that have either).

enter image description here

How would I go about writing this in PostgreSQL?


1 Answer 1


You could use LEFT JOIN LATERAL to get a list of all equipment names sorted alphabetically for each growth space, and compare it with another sorted list of elements:

SELECT gs.* 
FROM growth_spaces gs
  SELECT e.name
  FROM growth_space_equipment gse
  LEFT JOIN equipment e ON (gse.equipment_id = e.id)
  WHERE gse.growth_space_id = gs.id
  ORDER BY e.name) AS names) e ON (true)
WHERE e.names = ARRAY(SELECT UNNEST('{Mist Propagation,Automated Curtain}'::varchar[]) ORDER BY 1)

A variant of this is comparing a list of IDs rather than the texts, which would perform better for large sets:

SELECT gs.* 
FROM growth_spaces gs
LEFT JOIN LATERAL (SELECT ARRAY(SELECT equipment_id FROM growth_space_equipment WHERE growth_space_id = gs.id ORDER BY 1) AS eq_ids) e ON (true)
WHERE e.eq_ids = ARRAY(SELECT id FROM equipment WHERE name IN ('Mist Propagation', 'Automated Curtain') ORDER BY 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.