CODEWITHSUNDEEP
mysqlsql
Amiga500
Amiga500

Reputation: 6141

Simple select query in single table

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.

Upvotes: 0

Views: 108

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use GROUP BY clause :

SELECT o.user_id, o.name
FROM orders o
WHERE o.article_id IN (55, 115)
GROUP BY o.user_id, o.name
HAVING MIN(o.article_id) <> MAX(o.article_id);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271031

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

Upvotes: 1

Related Questions