SQL Select only rows where exact multiple relationships exist

Question

This is closely related to this question, but adds another requirement.

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 and only a specified set of relationships? E.g. with the sample data, how can I find all parents that have relationships with exactly property 5 and 1?

juergen d · Accepted Answer · 2021-11-09T14:21:17.577

14

SELECT PARENT_ID
FROM rel
GROUP BY PARENT_ID
HAVING SUM(PROP_ID NOT IN (5,1)) = 0
   AND SUM(PROP_ID = 1) = 1 
   AND SUM(PROP_ID = 5) = 1

edited Nov 09 '21 at 14:21

answered Jan 01 '13 at 19:00

juergen d

201,996
37
293
362

2

+1 but this isn't *exactly* right. It still works if one is missing. – Asad Saeeduddin Jan 01 '13 at 19:01
Thanks for the hint. I fixed that. – juergen d Jan 01 '13 at 19:02
Yes. I think that is what the OP wants. Otherwise I can be changed to `HAVING SUM(PROP_ID NOT IN (5,1)) = 0 AND SUM(PROP_ID = 1)=1 and SUM(PROP_ID = 5)=1` – juergen d Jan 01 '13 at 19:05
To quote the OP: "how can I find all parents that have relationships with **exactly** property 5 and 1" – Asad Saeeduddin Jan 01 '13 at 19:07
Unfortunately mySQL does not know the minus operator. You could then just remove the set coming from the not in operator from the set computed by the in operator. – sorencito Jan 01 '13 at 19:10
The first one is still not correct. It is possible to not have any `1`s and still fulfill the first condition, as long as you have two `5`s instead. – Asad Saeeduddin Jan 01 '13 at 19:11
@Asad: ok, ok. You win. :) – juergen d Jan 01 '13 at 19:12
1

@sorencito: No idea what you are talking about with `minus`operator. – juergen d Jan 01 '13 at 19:13
@juergend you could probably just use `HAVING Count(*)=SUM(PROP_ID IN (5,1))=2` – fthiella Jan 01 '13 at 19:19
1

@fthiella That doesn't quite work, because then two 5s and no 1s would still pass. – Asad Saeeduddin Jan 01 '13 at 19:19
@Asad unless there are multiple rows with same property and in which case you have to use DISTINCT, i believe it's should give the same result – fthiella Jan 01 '13 at 19:25
@juergend: Sry. Accepted. I just posted my own answer to illustrate the idea. – sorencito Jan 01 '13 at 20:58

score 4 · Answer 2 · answered Oct 28 '15 at 19:40

This alternative has the benefit of a constant statement structure and only one parameter, independent of the amount of relations you are looking for:

SELECT parent_id FROM rel 
GROUP BY parent_id 
HAVING GROUP_CONCAT(prop_id ORDER BY prop_id ASC SEPARATOR ",") = '1,5';

Disadvantages:

You need to prepare an ordered, comma separated String of prop_ids upfront.
This works on MySQL, but not all database servers.

score 3 · Answer 3 · edited May 23 '17 at 10:31

3

If you want to select all parents with at least a 5 and a 1, you can use:

SELECT PARENT_ID
FROM rel
GROUP BY PARENT_ID
HAVING SUM(PROP_ID = 1)
       AND SUM(PROP_ID = 5)
       AND SUM(PROP_ID NOT IN (5,1)) = 0

If you need exactly one 5 and one 1, see this answer

edited May 23 '17 at 10:31

Community

1
1

answered Jan 01 '13 at 19:09

Asad Saeeduddin

46,193
6
90
139

@juergend Yeah, but I just realised it is slightly wrong. It enforces at least one of each, but not exactly 1 of each. – Asad Saeeduddin Jan 01 '13 at 19:23

fthiella · Answer 4 · 2013-01-01T20:07:28.277

3

SELECT PARENT_ID
FROM rel
GROUP BY PARENT_ID
HAVING
  COUNT(PROP_ID)=2 AND
  COUNT(DISTINCT case when PROP_ID IN ( 1, 5 ) then PROP_ID end)=2

This will select all PARENT_ID that have exactly two rows, with exactly two, non duplicated, PROP_ID that match.

edited Jan 01 '13 at 20:07

answered Jan 01 '13 at 19:48

fthiella

48,073
15
90
106

Charles Bretana · Answer 5 · 2013-01-01T19:13:54.823

With two nested subqueries, like this..

 Select pa.Id
 From parents pa
 Where not exists -- This ensures that all specifies properties exist
    (Select * From property y
     Where propertyId In (1,5)
         And Not Exists
             (Select * From parentProperty
              Where parentId = pa.parentId 
                  And propertyId = y.propertyId ))
   And not exists -- This ensures that only specified list of properties exist
    (Select * From parentProperty
     Where parentId = pa.parentId 
        And propertyId Not In (1,5) )

The first one reads "Show me all the parents where there is not a property in the specified list of properties that is not in the parent properties table for the specified parent...."

The second subquery reads: "also make sure that there does not exist a record in the parentProperties table for that parent for any property that is not in the specified list."

lc. · Answer 6 · 2013-01-01T19:08:23.920

Assuming (PARENT_ID, PROP_ID) is unique:

SELECT r1.PARENT_ID
FROM rel r1
INNER JOIN rel r2 ON r1.PARENT_ID = r2.PARENT_ID AND r2.PROP_ID = 5 
INNER JOIN rel r3 ON r1.PARENT_ID = r3.PARENT_ID AND r3.PROP_ID = 1
GROUP BY r1.PARENT_ID
HAVING COUNT(*) = 2

Or,

SELECT parent.PARENT_ID
FROM parent
INNER JOIN
(
    SELECT PARENT_ID
    FROM rel
    WHERE PROP_ID IN (1,5)
    GROUP BY PARENT_ID
    HAVING COUNT(*) = 2
) good ON parent.PARENT_ID = good.PARENT_ID
LEFT OUTER JOIN rel bad ON parent.PARENT_ID = bad.PARENT_ID 
    AND bad.PROP_ID NOT IN (1,5)
WHERE bad.PARENT_ID IS NULL

Or even,

SELECT DISTINCT parent.PARENT_ID
FROM parent
INNER JOIN rel r2 ON parent.PARENT_ID = r2.PARENT_ID AND r2.PROP_ID = 5 
INNER JOIN rel r3 ON parent.PARENT_ID = r3.PARENT_ID AND r3.PROP_ID = 1
LEFT OUTER JOIN rel r0 ON parent.PARENT_ID = r0.PARENT_ID 
    AND r0.PROP_ID NOT IN (1,5)
WHERE r0.PARENT_ID IS NULL

sorencito · Answer 7 · 2013-01-02T08:15:26.367

0

If MySql supported minus, the query could look like this:

select parent_id
from rel
where prop_id in (5,1)
group by parent_id
having count(distinct prop_id)=2 and count(prop_id)=2
minus
select parent_id
from rel
where prop_id not in (5,1);

The not in will remove those relationships that exceed (5,1), e.g. (5,1,3).

I know you're using MySql and my answer hence is wrong. Just take it as another idea.

edited Jan 02 '13 at 08:15

answered Jan 01 '13 at 20:57

sorencito

2,517
20
21

MySql doesn't support minus, but even if it would, your query will return also parent_id where just one of the attributes matches, and also parent_id where duplicated attributes exists. pls see this fiddle http://sqlfiddle.com/#!4/ed577/6/0 second query should be correct – fthiella Jan 01 '13 at 21:38
@fthiella Thanks for your input - I corrected the query according to your fiddle. I didn't expect the table to contain duplicates and did not notice the mistake in the first IN clause. – sorencito Jan 02 '13 at 08:17

score 0 · Answer 8 · answered Mar 03 '13 at 06:15

This query is true even if (PARENT_ID, PROP_ID) is not unique:

SELECT PARENT_ID FROM rel WHERE
PROP_ID IN (5,1) AND 
PARENT_ID NOT IN (SELECT DISTINCT PARENT_ID FROM rel WHERE PROP_ID NOT IN (5,1))
GROUP BY PARENT_ID HAVING COUNT(DISTINCT PROP_ID) = 2

gezimi005 · Answer 9 · 2013-01-01T19:21:54.107

-1

Hope this will help you:

SELECT p.PARENT_ID , r.PROP_ID FROM rel r LEFT JOIN parent p ON p.PARENT_ID = r.PARENT_ID WHERE r.PROP_ID = 5 OR r.PROP_ID = 1

edited Jan 01 '13 at 19:21

answered Jan 01 '13 at 19:14

gezimi005

381
1
8

The output is wrong, as it contains duplicates and also those parent_ids with other relationships than (5,1). – sorencito Jan 01 '13 at 21:02

score -3 · Answer 10 · answered Jan 01 '13 at 18:59

-3

You can do this with a group by.

SELECT PARENT_ID FROM link_tbl WHERE PROP_ID IN (5,1) GROUP BY PARENT_ID HAVING COUNT(*) = 2

answered Jan 01 '13 at 18:59

user836087

2,271
8
23
33

The count in the `HAVING` is applied after the `WHERE` filter though, so they may have others. – Michael Berkowski Jan 01 '13 at 19:00

SQL Select only rows where exact multiple relationships exist

10 Answers10

Linked