3

I'm trying to get all duplicate rows from a DB. Lets say I have table entries with primary key id ant VARCHAR column called name. I already have a working SQL query:

SELECT id, entries.name FROM entries 
INNER JOIN 
    (SELECT name FROM entries 
     GROUP BY name 
     HAVING count(id) > 1) 
duplicate ON entries.name = duplicate.name

I'd like to construct similar query with CodeIgniter Datamapper lib for my personal interest as well as consistency with rest of the code.

I've build the subquery part and it is working as expected:

$e = new Entry();
$e->group_by('name')->having('COUNT(id) > 1')->get();

But I can't figure it out how can I integrate the subquery into parent query (the INNER JOIN ... ON ... part).

Cœur
  • 37,241
  • 25
  • 195
  • 267
package
  • 4,741
  • 1
  • 25
  • 35

1 Answers1

2

Firstly you need not to take inner join.

SELECT id, entries.name FROM entries GROUP BY name HAVING count(id) > 1

This will give you duplicate records. So you will not need to make data mapper for joined query.

You will get result by this only.

$e = new Entry();

$e->group_by('name')->having('COUNT(id) > 1')->get();

Update:

SELECT id, entries.name FROM entries where id NOT IN (SELECT id FROM entries GROUP BY name HAVING count(id) = 1)

Community
  • 1
  • 1
Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
  • If we had 3 rows in the table, for example: `1 John Doe`, `2 John Doe`, `3 Jane Doe`, this sub-query would return me only one row (`1 John Doe`). It does find the value that is duplicated, but it does not return all the duplicates. – package Feb 27 '12 at 07:07
  • Thanks, this one works. Although the performance on very small table is 2x worse when compared with `inner join`. I'll do some experiments. I'll award bounty to you if no better answer is given within couple of days. – package Feb 27 '12 at 08:55