CODEWITHSUNDEEP
sqljoin
Jeremy Ragsdale
Jeremy Ragsdale

Reputation: 95

sql, return rows when multiple rows have specific values?

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

Upvotes: 1

Views: 55

Answers (3)

randyh22
randyh22

Reputation: 473

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')

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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.

Upvotes: 1

LDMJoe
LDMJoe

Reputation: 1589

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

Upvotes: 1

Related Questions