CODEWITHSUNDEEP
mysql
Amiga500
Amiga500

Reputation: 6141

Selecting users from table with one occurence

I have a table orders: 

|--------------------|-----------------|------------|
|      user_id       |    article_id   |  Name      |
|--------------------|-----------------|------------|
|         1          |         39      |  John      |
|         1          |         39      |  John      |
|         2          |         39      |  Mike      |
|         2          |         19      |  Mike      |
|         3          |         39      |  Dave      |
|--------------------|-----------------|------------|

How can I select only those users who have article_id = 39, regardless of how many times, but if it occurs only once. I would need to select user John and user Dave, but not Mike. Mike has article_id 39, but he also has article_id = 19. The query should return users John and Dave.

I have tried this query:

SELECT * FROM orders AS o where o.article_id = 39
GROUP BY o.user_id HAVING COUNT(o.article_id) > 0

But it return all three users. I don't need user Mike.

Upvotes: 0

Views: 58

Answers (3)

rochy_01
rochy_01

Reputation: 161

Tim's answer above is the most efficient way to get what you're looking for. Another solution to this is to use a nested query.

First, you select all users who don't have article_id 39:

SELECT user_id FROM orders WHERE article_id <> 39

Then select users who have an article_id of 39 that are not in this list. Overall, the query would be:

SELECT user_id FROM orders WHERE article_id = 39 
AND user_id NOT IN (SELECT user_id from orders where article_id <> 39)

Again, the answers already posted by Tim and John are more efficient and better practice. I just find queries written like this easier to read/understand when learning SQL.

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

Use conditional aggregation in the HAVING clause:

SELECT user_id, Name
FROM orders
GROUP BY user_id, Name
HAVING SUM(CASE WHEN article_id <> 39 THEN 1 ELSE 0 END) = 0;

Note: Your table is not normalized, and the user information belongs in a separate user table. I was able to get around this by including the name in the GROUP BY clause, but rightfully we should just be aggregating by the user_id.

Upvotes: 6

John Woo
John Woo

Reputation: 263943

Another way, is to use MIN() and MAX()

SELECT  user_id
FROM    orders
GROUP   BY Name
HAVING  MIN(article_id) = MAX(article_id)
        AND MIN(article_id) = 39

Upvotes: 1

Related Questions