Codeigniter从数据库中获取所有重复记录并在视图中显示

Question

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.

Answer 1

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();