Laizer Laizer - 6 months ago 17
MySQL Question

SQL Select only rows where multiple relationships exist

Given a parent table 'parent'

╔═══════════╦══════════╗
║ PARENT_ID ║ NAME ║
╠═══════════╬══════════╣
║ 1 ║ bob ║
║ 2 ║ carol ║
║ 3 ║ stew ║
╚═══════════╩══════════╝


and a many-many relationship table 'rel' between parent and a (here unspecified) property table

╔═══════════╦═══════════╗
║ PARENT_ID ║ PROP_ID ║
╠═══════════╬═══════════╣
║ 1 ║ 5 ║
║ 1 ║ 1 ║
║ 2 ║ 5 ║
║ 2 ║ 4 ║
║ 2 ║ 1 ║
║ 3 ║ 1 ║
║ 3 ║ 3 ║
╚═══════════╩═══════════╝


How can I select all parents that have all of a specified set of relationships? E.g. with the sample data, how can I find all parents that have both property 5 and 1?




edit:
Same question but with requirement for an exact match:
SQL Select only rows where exact multiple relationships exist

Answer

This is called Relational Division

SELECT  a.name
FROM    parent a
        INNER JOIN rel b
            ON a.parent_ID = b.parent_ID
WHERE   b.prop_id IN (1,5)
GROUP BY a.name
HAVING COUNT(*) = 2

UPDATE 1

if unique constraint was not enforce on prop_id for every parent_id, DISTINCT is needed on this case.

SELECT  a.name
FROM    parent a
        INNER JOIN rel b
            ON a.parent_ID = b.parent_ID
WHERE   b.prop_id IN (1,5)
GROUP BY a.name
HAVING COUNT(DISTINCT b.prop_id) = 2