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?
Best Answer
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:A variant of this is comparing a list of IDs rather than the texts, which would perform better for large sets: