sql, return rows when multiple rows have specific values?

Question

Sorry for the title but that was the best way I could think to explain it... Here is my scenario

I have a table that stores rows for communications with my clients. Something like this

|  UserID  |  CommunicationID
-----------------------------
|  User1   |  com1              
|  User1   |  com2              
|  User1   |  com3              
|  User2   |  com1              
|  User2   |  com2              
|  User3   |  com1

What I am looking for is a query that returns the UserID only for the Users who have received ALL 3 communications. So in the example above, only User1 would be returned.

I forgot to mention that I only need records where the User specifically received Com1 & Com2 & Com3. Regardless of how many times they received any one com, they have to had received all 3.

This problem came about because some users have mistakenly received com1 many times and it's thrown of my queries to identify people properly.

I had a stab at it myself trying CommunicationID IN( Com1, Com2, Com3) but of course that returns everyone. I though also about joining the table back to itself but not 100% sure how that would work.

Any sql gurus out there I would love your advice.

Thanks

What is the index on this table? Is it possible for User1 to have one of the communication values more than once? — octopusgrabbus
– octopusgrabbus, Commented Jan 29, 2016 at 17:08

Giorgos Betsos · Accepted Answer · 2016-01-29 16:32:43Z

1

Try this:

SELECT UserID
FROM mytable
WHERE CommunicationID IN ('Com1', 'Com2', 'Com3') 
GROUP BY UserID
HAVING COUNT(DISTINCT CommunicationID) = 3

The above query selects the UserID values of all users having all three CommunicationID values, irrespective of how many times each distinct CommunicationID value appears in the table.

edited Jan 29, 2016 at 16:32

answered Jan 29, 2016 at 16:21

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeremy Ragsdale Over a year ago

I could see how that could work. How about the case when User2 mistakenly gets Com1 3times but no other, he would then be identified also. I should have added I need only Users who specifically received Com 1 & Com2 & Com3 regardless of how many times they have received any one of them

Giorgos Betsos Over a year ago

@JeremyRagsdale No, he wouldn't be identified. That's what DISTINCT in COUNT essentially does: it counts only distinct CommunicationID values.

LDMJoe · Accepted Answer · 2016-01-29 16:28:01Z

This approach uses WHERE clauses to treat it as three different elements, joining them to each other. At the end of the join, only those that have survived all three hops will be left in your result set.

SELECT
    DerivedCom3.UserID
FROM
    (
    SELECT DISTINCT
        UserID
    FROM
        YourTable
    WHERE
        CommunicationID = 'com1'
    ) DerivedCom1
    LEFT OUTER JOIN
    (
    SELECT DISTINCT
        UserID
    FROM
        YourTable
    WHERE
        CommunicationID = 'com2'
    ) DerivedCom2 ON DerivedCom2.UserID = DerivedCom1.UserID
    LEFT OUTER JOIN 
    (
    SELECT DISTINCT
        UserID
    FROM
        YourTable
    WHERE
        CommunicationID = 'com3'
    ) DerivedCom3 ON DerivedCom2.UserID = DerivedCom3.UserID
WHERE
    DerivedCom3.UserID IS NOT NULL

randyh22 · Accepted Answer · 2016-01-29 17:00:03Z

1

This would work but could be slow depending on how big the table and how it is indexed.

SELECT UserID
FROM mytable
WHERE UserID IN(SELECT UserID FROM mytable WHERE CommunicationID = 'Com1')
AND UserID IN(SELECT UserID FROM mytable WHERE CommunicationID = 'Com2')
and UserID IN(SELECT UserID FROM mytable WHERE CommunicationID = 'Com3')

edited Jan 29, 2016 at 17:00

user330315

answered Jan 29, 2016 at 16:30

randyh22

4734 silver badges10 bronze badges

Collectives™ on Stack Overflow

sql, return rows when multiple rows have specific values?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related