CODEWITHSUNDEEP
mysqlsql
Kash
Kash

Reputation: 43

MYSQL - Get all records that have more than 1 record for the same id

I apologize in advanced if I am not explaining this correctly. I can barely explain it in english terms, let alone in a mysql query.

I am trying to get the list of response_set_ids that have more than 1 record for a question_id.

This is an example of my data:

+----+-----------------+-------------+-----------+
| id | response_set_id | question_id | answer_id |
+----+-----------------+-------------+-----------+
|  1 |              10 |           1 |         4 |
|  2 |              10 |           2 |         5 |
|  3 |              10 |           3 |         6 |
|  4 |              10 |           3 |         7 |
|  5 |              11 |           1 |         8 |
|  6 |              11 |           2 |         9 |
|  7 |              11 |           3 |        10 |
+----+-----------------+-------------+-----------+

I would like to have a query that would return me a list response_set_ids, and in this particular example, I would expect to get returned 10 because that response_set has question_id -> 3 showing up more than once.

Please let me know if you need any further information to help me.

I have tried this: select response_set_id, count(question_id) from responses group by response_set_id;

But that only gives me the counts of questions per response_set.

Thank you in advanced!

Upvotes: 4

Views: 4532

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271191

The simplest method doesn't use a subquery:

SELECT DISTINCT response_set_id
FROM responses
GROUP BY response_set_id, question_id
HAVING COUNT(*) > 1;

This is one of the very, very few instances where select distinct is used (appropriately) with group by.

Upvotes: 2

Daniel Marcus
Daniel Marcus

Reputation: 2696

select distinct response_set_id from (
    select  response_set_id , question_id  
    from 
    responses
    group by 
    response_set_id, question_id
    having count(*)>1) a

Upvotes: 1

CSSBurner
CSSBurner

Reputation: 1991

I believe this question has been asked before but I cannot add comments at my current rep:

SELECT DISTINCT response_set_id
FROM responses
GROUP BY question_id
HAVING COUNT(question_id) > 1

Upvotes: 0

Related Questions