CODEWITHSUNDEEP
sqlsql-server
Danny
Danny

Reputation: 41

SQL matching multiple values to various values in same table

I am having some issues in trying to get my query to work. I am not that efficient with SQL Server.

I have a table that has an user id column and the user id matches different values in the type column so the data looks like this

User | Type 
User1 | Soccer 
User1 | Tennis 
User1 | BasketBall 
User2 | Tennis 
User2 | Swimming 
User3 | Soccer 
User3 | Swimming

I want to be able to get all users that that belong to only one type (Soccer) but do not belong to any other types. So they should only have one type they belong to in the database.

Upvotes: 3

Views: 229

Answers (4)

Himanshu
Himanshu

Reputation: 32602

Try HAVING with self-join:

SELECT T1.[User] FROM MyTable T1
  JOIN MyTable T2
    ON T1.[User] = T2.[User]
   AND T2.[Type] = 'Soccer'
GROUP BY T1.[User]
HAVING COUNT(T1.[Type]) = 1;

See this SQLFiddle

Upvotes: 4

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT t1.[User] 
FROM [MyTable] t1
WHERE t1.Type = 'Soccer'
AND NOT EXISTS (SELECT 1
                FROM MyTable t2
                WHERE t2.[User] = t1.[User] 
                AND t2.Type != t1.Type)

Upvotes: 1

bvr
bvr

Reputation: 4826

Try this

SELECT [User] FROM Table1 T1
WHERE EXISTS (SELECT T2.[User] FROM Table1 T2 WHERE T2.[User] = T1.[User]
             AND T2.[Type] = 'Soccer')
GROUP BY [User]
HAVING COUNT([User]) = 1

Upvotes: 1

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

SELECT [User] FROM Table1
GROUP BY [User]
HAVING COUNT([Type]) = 1
and max([Type])='Soccer'
and min([Type])='Soccer'

SQL FIDDLE

Upvotes: 4

Related Questions