Welcome Guest, Not a member yet? Register   Sign In
Active record INNER JOIN to search for duplicate records
#1

[eluser]Unknown[/eluser]
Hello folks,

Using the active record syntax, I am trying to find duplicate records based on two fields. The query works, but it returns all records from the table (instead of only the duplicates. I cannot figure out what I am doing wrong. Any help would be greatly appreciated.

Code:
$this->db->from('shipments a');
$this->db->join('shipments b', 'a.cust_name=b.cust_name AND a.address1=b.address1', 'inner');
$this->db->where('a.id !=', 'b.id');
$this->db->order_by('a.cust_name');
return $this->db->get()->result();

Additionally, I have tried:
Code:
$this->db->where('a.id <>', 'b.id');
in the where clause.

Thanks! Smile
#2

[eluser]CroNiX[/eluser]
COUNT() the duplicates in the query and use a HAVING count > 1
#3

[eluser]Unknown[/eluser]
Thanks for the reply. I tried using COUNT with HAVING, but with that method the results only show 1 row for each group of duplicates. I need to show each duplicate record individually.




Theme © iAndrew 2016 - Forum software by © MyBB