17

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

Community
  • 1
  • 1
Laizer
  • 5,932
  • 7
  • 46
  • 73

4 Answers4

20

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
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • This assumes that the combination of parent_id and prop_id are always unique. You could modify the `HAVING` clause to return more precisely: `HAVING COUNT(DISTINCT b.prop_id) = 2` –  Dec 31 '12 at 06:30
  • @Nicarus yes, as what the example records have shown. but if they are not unique assuming there is another field on the table which make the columns unique, then a `DISTINCT` keyword is required. – John Woo Dec 31 '12 at 06:33
  • I'm curious - why the necessity for the 'count' clause? – Laizer Dec 31 '12 at 06:35
  • the number of records returned on the query is equal to the number of values on the condition, try executing this, `SELECT a.name FROM parent a INNER JOIN rel b ON a.parent_ID = b.parent_ID WHERE b.prop_id IN (1,5)` and see the difference. – John Woo Dec 31 '12 at 06:37
  • I'm realizing that I missed a piece here. Posted the additional requirement under: http://stackoverflow.com/questions/14113267/sql-select-only-rows-where-exact-multiple-relationships-exist – Laizer Jan 01 '13 at 18:57
1

name your first table a, and second table b

SELECT parent_id FROM prop b1 
WHERE prop_id=1 and 
EXISTS (SELECT parent_id FROM prop b2 
        WHERE b2.parent_id=b1.parent_id AND b2.prop_id=5)
buaacss
  • 757
  • 6
  • 10
1

I just saw this solution to a different question that seems to fit this case:

 SELECT distinct parent_id
 FROM rel as T1
 INNER JOIN rel as T2
 ON T1.parent_id = T2.parent_id
 WHERE T1.prop_id = '1' and T2.prop_id = '5'
Community
  • 1
  • 1
Laizer
  • 5,932
  • 7
  • 46
  • 73
  • 3
    what if you need to find another `prop_id`? you need another join again? waste of resources. – John Woo Dec 31 '12 at 06:34
  • It might be waste of computing resources, however the answer is totally valid and might save a lot of thinking resources for the programmer so it's worth the consideration. Don't optimize prematurely! – Lars Blumberg Dec 06 '17 at 08:28
1

I've written your table in to a CTE, let me know if you require assistance in adapting the code for your purposes.

;WITH MyTable AS
(
    SELECT   parent_id = 1
            ,prop_id = 5    UNION ALL
    SELECT 1,1              UNION ALL
    SELECT 2,5              UNION ALL
    SELECT 2,4              UNION ALL
    SELECT 2,1              UNION ALL
    SELECT 3,1              UNION ALL
    SELECT 3,3              
)
,Eval AS
(
    SELECT   parent_id
            ,PropEval   = SUM(CASE WHEN prop_id IN (1,5) THEN 1 ELSE 0 END)
    FROM MyTable
    GROUP BY parent_id
)
SELECT parent_id
FROM Eval
WHERE PropEval = 2
MarkD
  • 5,276
  • 1
  • 14
  • 22