Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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).
How would I go about writing this in PostgreSQL?
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
LEFT JOIN LATERAL (SELECT ARRAY(
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)
