mysql - Simple select query in single table

Question

I have table orders, it has many columns, but I am interested in these, user_id and article_id.

|--------------------|-----------------|------------|
|      user_id       |    article_id   |  Name      |
|--------------------|-----------------|------------|
|         1          |         115     |  John      |
|         1          |         55      |  John      |
|         2          |         115     |  Mike      |
|         2          |         49      |  Mike      |
|         3          |         115     |  Dave      |
|         3          |         55      |  Dave      |
|--------------------|-----------------|------------|

I need to make a query that selects those users that have article_id 115 and 55. Desired result in this example is to get users named John and Dave, since both of them have 115 and 55. There is no column Name, I just added it to for better understanding.

I have tried this query:

SELECT * FROM orders AS o
WHERE o.article_id IN (55, 115)
ORDER BY o.user_id 

But I get all article_id and user_id. I know I should use another select to select only those users with 55 and 115... but I am stuck.

标签： mysqlsql

Answer 1

Use group by and having. Assuming you don't have duplicates in the table:

SELECT o.user_id, o.name
FROM orders o
WHERE o.article_id IN (55, 115)
GROUP BY o.user_id, o.name
HAVING COUNT(*) = 2;  -- has both