CODEWITHSUNDEEP
mysql
Peter
Peter

Reputation: 9123

Finding duplicate rows in a MySQL table

I have a table in which I link items to types where each type can have multiple categories. For each combination of type and category, only one item should be linked. However, through some past error of mine, some duplicates have slipped through. I am now trying to write a query that will give me the duplicates but I am not doing a great job or I wouldn't be posting here obviously.

SELECT
    item_id,
    type_id,
    category
FROM itemTypes
WHERE category = 'cat1'
GROUP BY type_id
HAVING COUNT(*) >= 2;

This is what I tried. It does work and gives me the type_ids that are linked to different items. But each type_id should be linked to only one item. This list doesn't show me the items that are linked. And that is just the thing I would like to know.

Can someone help me out?

Update

Below is a data sample. As you can see type_id 5 and 6 are linked multiple times. What I would like to get as a result is only these records.

| id    | item_id   | type_id   |   cat     |
+-------+-----------+-----------+-----------+
| 1     | 100       | 5         | cat1      |
| 2     | 110       | 5         | cat1      |
| 3     | 115       | 6         | cat1      |
| 4     | 120       | 7         | cat1      |
| 5     | 125       | 5         | cat1      |
| 6     | 130       | 6         | cat1      |
| 7     | 135       | 4         | cat1      |
| 8     | 140       | 8         | cat1      |

Upvotes: 0

Views: 27

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

You need to join your itemTypes table to the query you currently have, rephrased as a subquery:

SELECT t1.*
FROM itemTypes t1
INNER JOIN
(
    SELECT item_id
    FROM itemTypes
    WHERE category = 'cat1'
    GROUP BY item_id
    HAVING COUNT(*) > 1
) t2
    ON t1.item_id = t2.item_id;

The logical problem with your current query is that it can only find item_id values which meet your criteria, but not the other column values.

Upvotes: 2

Related Questions