0

I have a master record of candidate with passport numbers lets say (table1), another record with candidates passport numbers in it(table2). Now I want to find and show all duplicate passport numbers and their data matching passport number from table2 in table1

For eg :

    table1 :
    id | name | passport_number | test_date
    1 | jane doe | a123456 | 1-Jan-2017
    2 | jane doe | a123456 | 2-Jan-2017
    3 | jane doe | a123456 | 3-Jan-2017
    4 | doe jane | b123456 | 1-Jan-2017
    5 | doe jane | b123456 | 2-Jan-2017
    6 | name | d123456 | 5-Jan-2017

    table2:
    id | passport_number | test_date
    1 |  a123456 | 1-Jan-2017
    2 |  c123456 | 4-Jan-2017
    3 |  a123456 | 2-Jan-2017
    4 |  b123456 | 1-Jan-2017
    5 |  b123456 | 2-Jan-2017

Results should be:
 id | name | passport_number | test_date
    1 | jane doe | a123456 | 1-Jan-2017
    2 | jane doe | a123456 | 2-Jan-2017
    3 | jane doe | a123456 | 3-Jan-2017
    4 | doe jane | b123456 | 1-Jan-2017
    5 | doe jane | b123456 | 2-Jan-2017

Now I want to get all data of passport_numbers (only if the candidate is appeared in two test dates in table1) from table2 comparing it with table1 passport_number and see how many times this candidate has given test in previous dates. It should show all duplicate entries of passport not just 1 entry for group_by or count.

MysticalSam
  • 51
  • 1
  • 10
  • 1
    [Here's a bunch of not-CI-specific answers to find duplicates in mysql.](http://stackoverflow.com/questions/688549/finding-duplicate-values-in-) – ourmandave Mar 11 '17 at 16:34

2 Answers2

0

Your query could be something like this

$this->db->select('*');
$this->db->from('table1');
$this->db->join('table2', 'table1.passport_number  = table2.passport_number ', 'left'); 
$query = $this->db->get();
return $query->result();
Amr Aly
  • 3,871
  • 2
  • 16
  • 33
0
$this->db->select("t1.*", FALSE);
$this->db->from("table1 t1, table2 t2");
$this->db->where("t1.passport_number", "t2.passport_number", FALSE);
$this->db->group_by("t1.id");
$result = $this->db->get();
Elena Vasilenko
  • 236
  • 1
  • 7
  • Thanks Elena this one works for me but there is slight problem, When I uses group_by("passport_number"), it messes up with results, and it is not grouping same passport numbers. Any ideas ? – MysticalSam Mar 14 '17 at 04:43
  • If you used my query, then should be $this->db->group_by("t1.passport_number"); - it returns 2 rows (id=1 and id=4). – Elena Vasilenko Mar 15 '17 at 04:05