CODEWITHSUNDEEP
mysqlsubquery
Vinay Gurram
Vinay Gurram

Reputation: 73

MySQL Query to select users opted for only one category

I have a table with data similar to below

id  UserId  Category
1    5001       A
2    5001       B
3    5002       A
4    5003       B
5    5004       A
6    5001       A

1) How do we query table which can fetch me users who have registered only for A category. The user should not be registered for B or any other category. For the data above, it should return me only 

3    5002       A

2) How do we write a query which can fetch the users who have registered for both A and B category. Not alone A and Not alone B For the above, it should return me only 

userId - 5001

Can someone help on this ?

Upvotes: 1

Views: 306

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43594

You can use the following solution, using IN:

SELECT DISTINCT UserID, Category
FROM table_name t
WHERE Category = 'A' AND UserID NOT IN (
    SELECT UserID 
    FROM table_name
    WHERE Category <> t.Category
);

Another solution using EXISTS:

SELECT DISTINCT UserID, Category
FROM table_name t
WHERE Category = 'A' AND NOT EXISTS (
    SELECT 1 
    FROM table_name 
    WHERE UserID = t.UserID AND Category <> t.Category
);

To get the second result, you can use the following solution using a INNER JOIN:

SELECT DISTINCT t.UserId
FROM table_name t INNER JOIN (
    SELECT UserId 
    FROM table_name
    WHERE Category = 'B'
) b ON t.UserId = b.UserId
WHERE t.Category = 'A'

Another solution using EXISTS:

SELECT DISTINCT UserID
FROM table_name t
WHERE Category = 'A' AND EXISTS (
    SELECT 1 
    FROM table_name
    WHERE UserID = t.UserID AND Category = 'B'
);

demo on dbfiddle.uk

Upvotes: 1

Related Questions