SQL matching multiple values to various values in same table

Question

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.

Trick Question :)

Prahalad Gaggar
– Prahalad Gaggar

2013-07-05 05:09:31 +00:00
Commented Jul 5, 2013 at 5:09 — Prahalad Gaggar
– Prahalad Gaggar, Commented Jul 5, 2013 at 5:09

Prahalad Gaggar · Accepted Answer · 2013-07-05 05:12:52Z

4

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

SQL FIDDLE

edited Jul 5, 2013 at 5:12

answered Jul 5, 2013 at 4:58

Prahalad Gaggar

11.6k17 gold badges58 silver badges73 bronze badges

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

Comments

Himanshu · Accepted Answer · 2013-07-05 05:34:49Z

4

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

edited Jul 5, 2013 at 5:34

answered Jul 5, 2013 at 4:56

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

3 Comments

JanR Over a year ago

you will need to add a where clause with WHERE type = 'Soccer'

Prahalad Gaggar Over a year ago

@hims056 Please see your answer Logically you are wrong.

Danny Over a year ago

Thank hims056! It worked. I appreciate the help. Still trying to weed myself through sql

bvr · Accepted Answer · 2013-07-05 05:19:21Z

1

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

edited Jul 5, 2013 at 5:19

answered Jul 5, 2013 at 4:58

bvr

4,8361 gold badge23 silver badges24 bronze badges

1 Comment

Prahalad Gaggar Over a year ago

Please see your answer Logically you are wrong.

Justin · Accepted Answer · 2013-07-05 05:31:00Z

1

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)

edited Jul 5, 2013 at 5:31

answered Jul 5, 2013 at 5:04

Justin

9,7336 gold badges39 silver badges49 bronze badges

Collectives™ on Stack Overflow

SQL matching multiple values to various values in same table

4 Answers 4

Comments

See this SQLFiddle

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

See this SQLFiddle

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related