SELECT WHERE (1 AND 2) IN GROUP_CONCAT()

Question

entity
---
id  name
---
1   one
2   two
3   three

property
---
id  name
---
1   prop1
2   prop2
3   prop3

entity_property
---
entity_id   property_id
---
1           1
1           2
1           3
2           1

I wanna get entities that have at least 1 and 2 (but can have even more) properties.

This is a workaround I don't like:

SELECT entity_property.entity_id,
(GROUP_CONCAT(entity_property.property_id)) as props
FROM `entity_property` 
JOIN entity
ON entity_property.entity_id = entity.id
GROUP BY entity.id

It returns:

entity_id props
---
1   1,2,3
2   1

Then I have to explode it with server language and then exclude.

---

This query returns all entity's rows:

SELECT entity.id
FROM entity
WHERE (1 AND 2) IN
    (SELECT property_id
     FROM entity_property
     LEFT JOIN entity 
     ON entity_property.entity_id = entity.id
     WHERE entity_property.entity_id = entity.id)

---

This query causes error:

SELECT entity.id as ent_id
FROM entity
WHERE (1 AND 2) IN
    (SELECT property_id
     FROM entity_property
     LEFT JOIN entity 
     ON entity_property.entity_id = entity.id
     WHERE entity_property.entity_id = ent_id)

Answer 1

You can get the entity ids using group by and having:

SELECT ep.entity_id
FROM `entity_property` ep
WHERE ep.property_id IN (1, 2)
GROUP BY ep.entity_id
HAVING COUNT(DISTINCT ep.property_id) = 2;

Notes:

• This does not require the entity table.
• The DISTINCT is not necessary if duplicate pairs are not allowed in entity_property.
• For more properties, you need to change bother the WHERE and the HAVING (where "2" is the number of things you want to match).

Answer 2

You are interested in all records from entity that have a record in entity_property with a value of 1 or 2. To achieve this you could just inner join entity_property with a limiting factor, for example

SELECT
    e.*
FROM entity e
INNER JOIN entity_property ep1
    ON ep1.entity_id = e.id
    AND ep1.property_id = 1
INNER JOIN entity_property ep2
    ON ep2.entity_id = e.id
    AND ep2.property_id = 2

The inner joins ensure only records from entity with a corresponding row in entity_property are returned. The double-join lets you ensure that the entity rows have both 1 and 2. The use of simple joins allow use of indexes, for example an index on entity_property ( property_id , entity_id )

Answer 3

You can find the entity_id having property_ids atleast 1, 2 using conditional having clause and get the group_concat on that.

Try this:

select entity_id, group_concat(property_id)
from entity_property
group by entity_id
having 
count(distinct 
        case when property_id in (1, 2)
        then property_id end) = 2;