Welcome Guest, Not a member yet? Register   Sign In
How to get distinct results joining 3 MySql tables in Codeigniter?
#1

I have three tables in my database. See my tables--
[color=var(--highlight-color)]Users Table:
[/color]
Code:
user_id  name      email      phone    password   role
  38     Asif      ----        ----      ----      ---
  39     Anil      ----        ----      ----      ---



Code:
Flats Table:
flat_id  block  flat_number  owner_id  area  rate
  1       B       B101        38       ---    ---
  2       A       A101        39       ---    ---


[color=var(--highlight-color)]Payments Table:
[/color]
Code:
pay_id  flat_id   month_from   month_to  amount  date
  1        1        ------      ------    ----    ---
  2        2        ------      ------    ----    ---
 28        1        ------      ------    ----    ---


When I join these table using this code:

Code:
$db      = \Config\Database::connect();
$builder = $db->table('payments p');
$builder->select('*');
$builder->join('flats f', 'f.flat_id = p.flat_id', 'left');
$builder->join('users u', 'u.user_id = f.owner_id', 'left');
$query = $builder->get();
$data['table_joined'] = $query->getResult();


I get these results:

Code:
FlatNumber  User    PaidUpto    LastPaid
B101        Asif  Sep 5, 2020    ----
A101        Anil  Oct 5, 2020    ----
B101        Asif  Oct 5, 2020    ----

According to my requirement, the result shouldn't have duplicate User or FlatNumber. There should be results equal to the number of User in users table.. Plz suggest me changes in my Join Query.

Thanks in Advance!
Reply
#2

I know this is late but may help someone

PHP Code:
$db      = \Config\Database::connect();
$builder $db->table('payments p');
$builder->select('*');
$builder->join('flats f''f.flat_id = p.flat_id''left');
$builder->join('users u''u.user_id = f.owner_id''left');
$builder->groupBy('name');
$query $builder->get();
$data['table_joined'] = $query->getResult(); 
Reply




Theme © iAndrew 2016 - Forum software by © MyBB