Retrieve Multiple Rows With Query Using And And Or

I want to retrieve multiple rows using same id's. Therefore having this table 'component_property', I would like to have as results 2 records, id's: 8 and 9 according to my SQL que

Solution 1:

This is a case of relational division:

SELECT c.id, c.name
FROM   components_componentproperty cp1
JOIN   components_componentproperty cp2 USING (component_id)
JOIN   components_component         c   ON c.id = cp1.component_id
WHERE  cp1.property_id = 9102AND cp1.value IN ('4015', '4016')AND    cp2.property_id = 8801AND cp2.value = '3'AND    c.type_id = 3832GROUPBY c.id;

We have assembled an arsenal of relevant techniques here:

• How to filter SQL results in a has-many-through relation

Check for a large number of properties

You can expand the above query and for a hand full of properties it will be among the fastest possible solutions. For a bigger number it will be more convenient (and also starting to be faster) to go this route:

Example for 5 properties, expand as needed:

SELECT c.id, c.name
FROM  (
   SELECT id
   FROM  (
      SELECT component_id AS id, property_id  -- alias id just to shorten syntaxFROM   components_componentproperty
      WHERE  property_id IN (9102, 8801, 1234, 5678, 9876)  -- expand as neededGROUPBY1,2
      ) cp1
   GROUPBY1HAVINGcount(*) =5-- match IN expression
   ) cp2
JOIN   components_component c USING (id);

The extra step of the inner subquery cp1 is only necessary, because you obviously have multiple entries per (component_id, property_id) in components_componentproperty. We could fold cp1 and cp2 into one and check

HAVINGcount(DISTINCT property_id) =5

But I expect that to be more expensive, since count(DISTINCT col) needs one sort operation per row.

For very long lists IN is a bad choice. Consider:

• Optimizing a Postgres query with a large IN