CODEWITHSUNDEEP
sqlselect
user3461809
user3461809

Reputation: 3

Select only the persons that have both values

I have a table with 2 columns:

ClientID | PhoneType
    1    |  4
    1    |  4
    1    |  5
    2    |  4
    3    |  5

I'm trying to wright a query to only show the client who have both phone types. So output should only show client ID 1

As you notice, client 1 has two type '4' PhoneTypes, so I'm trying not to use a GroupBy/HavingCount of method to narrow the result down. Which were the only examples I could find.

Upvotes: 0

Views: 102

Answers (2)

Gavin
Gavin

Reputation: 5871

Something like...

SELECT x.ClientID
FROM (
   SELECT DISTINCT ClientID, PhoneType FROM [Table A]
) AS x
GROUP BY x.ClientID
HAVING COUNT(x.PhoneType) = 2

DISTINCT is used to remove duplicates from the COUNT in case the client has 2 or more phones of the same type.

Upvotes: 2

Jeff
Jeff

Reputation: 12785

I don't have access to a SQL Server currently, but it seems like you should be able to use the following, which is a little simpler than Gavin's answer:

SELECT ClientID
FROM [Table A]
GROUP BY ClientID
HAVING COUNT(DISTINCT PhoneType) = 2

Not entirely sure that count distinct works in HAVING, but I don't see why it wouldn't...

Upvotes: 0

Related Questions