Finding duplicate rows in a MySQL table

Peter

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      |
Tim Biegeleisen

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

MySQL delete duplicate rows in table

From Dev

MySQL - Add duplicate rows to archive table, then delete duplicate rows

From Dev

Remove duplicate rows on many to many table (Mysql)

From Dev

Remove duplicate rows on many to many table (Mysql)

From Dev

mysql delete duplicate rows from table

From Dev

MySQL querying a junction table returns duplicate rows

From Dev

MYSQL - Finding value on table when all rows on different table exist

From Java

Finding duplicate values in MySQL

From Dev

finding duplicate rows counts by keeping all rows

From Java

Finding duplicate values in a SQL table

From Dev

Finding duplicate model number in Mysql

From Dev

MySQL finding duplicate records with AND statement

From Dev

MySQL finding duplicate items in a row

From Dev

Finding a keyword in duplicate values in MYSQL

From Dev

populating html table with mySQL data in php without adding duplicate rows

From Dev

Remove duplicate rows from MySQL table (ignore chars and whitespace)

From Dev

Find duplicate rows in a table

From Dev

Delete duplicate rows in a table

From Dev

duplicate rows MySQL

From Dev

Duplicate rows in mysql

From Dev

Delete duplicate rows in mysql

From Dev

duplicate rows MySQL

From Dev

MySQL IN() with duplicate rows

From Dev

counting duplicate rows in mysql

From Dev

Finding all duplicate rows with the given conditions

From Dev

Finding Rows containing % symbol in a table

From Dev

mysql How to Find Difference between two rows in same table and list the Unmatched Records? mysql finding unmatched rows in a table

From Dev

MySQL Finding column values in rows

From Dev

MySQL Finding column values in rows

Related Related

HotTag

Archive