Get the Record where only Two Records exists in SQL

All we need is an easy explanation of the problem, so here it is.

I want to get the parentID where user1 and user2 are using and only the two of them are using.

for example,

ParentTbl
| ID   |           Remarks              |
|------|--------------------------------|
| 1    | only one use this              |
| 2    | two user, _this is the ouput_  |
| 3    | three users use this           |

UserTbl
| ID | UserID | ParentID   |
|----|--------|------------|
| 1  |    1   |      1     |
|_2_ | ___1___|   ___2___  |
|_3_ | ___2___|   ___2___  |
| 4  |    1   |      3     |
| 5  |    2   |      3     |
| 6  |    3   |      3     |

It should be Parent 2

I already have :

select ParentID from UserTbl 
where UserID IN (1,2)
group by ParentID
having COUNT(*) = 2 

But the output is wrong.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If I understand you correctly, what you want is

SELECT ParentID
FROM   UserTbl 
GROUP BY ParentID 
HAVING SUM(CASE WHEN userID IN (1, 2) THEN 1 ELSE 0 END) = 2
AND    COUNT(*) = 2

The first condition SUM(CASE WHEN userID IN (1, 2) THEN 1 ELSE 0 END) = 2 is to check User 1 and 2 are in

The second condition COUNT(*) = 2 users count = 2 (only user 1 and 2)

Method 2

I arrived to this answer, but I am not sure if it will give me the best performance.

I added a condition that excludes the invalid IDs where it is not equal to the specified users.

select parentid from UserTbl 
    WHERE parentID NOT IN (
                          SELECT parentID 
                          FROM UserTbl
                          WHERE userid NOT IN (1,2))
    AND userid IN (3,2)
    GROUP BY parentID 
    HAVING COUNT(DISTINCT(userid)) = 2

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply