CODEWITHSUNDEEP
mysql
Majesty
Majesty

Reputation: 1909

MYSQL: find duplicates in a relational field

I have a main table, related to another table through another_table_id field.

id  another_table_id
1   1
2   2
3   2

I need to select all rows, that have duplicates (id 2 and 3 of main table)

I tried the following query

select * from main_table
group by another_table_id
having count(*) > 1

But the query gives me

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'main_table.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Also, this query 

select * from main_table
group by id, another_table_id
having count(*) > 1

Give me an empty dataset, any suggestions, please?

Upvotes: 0

Views: 34

Answers (2)

Strawberry
Strawberry

Reputation: 33935

Here's one way:

SELECT DISTINCT x.* 
           FROM my_table x 
           JOIN my_table y 
             ON y.another_table_id = x.another_table_id 
            AND y.id <> x.id;

Upvotes: 3

Michael Staples
Michael Staples

Reputation: 177

You could use a JOIN.

SELECT * FROM main_table
JOIN another_table ON another_table.id = main_table.

This would join the data eliminating the need for the HAVING clause

However if you wanted both sets of data use a UNION as this will not join the data

SELECT * FROM main_table
UNION 
SELECT * FROM another_table
HAVING COUNT(*) > 1

Hope this helps!

Upvotes: 0

Related Questions