how can we join this two table results in to one

Question

Here i want to to join this two query results in to one result the first query looks like this

   $this->db->select('bills.date as d_date,bill_details.agent_name,
   SUM(bill_details.profit) AS total_profit');
   $this->db->join('bill_details', 'bill_details.bill_id=bills.id','left'); 
   $this->db->where('DATE(bills.date) >=', $start_date);
   $this->db->where('DATE(bills.date) <=', $end_date); 
   $this->db->group_by('Date(bills.date)');
   $this->db->group_by('bills.customerid');
   $query1 = $this->db->get('bills')->result();
   return $query1;

the result looks like this

Name    Date          Purchase  
Ned     2019-07-26      210.60

the second query looks like this

    $this->db->select('assigned_result.date,assigned_result.user_id,SUM(assigned_result.total_price) AS t_price,SUM(assigned_result.total_dc) AS t_dc');
    $this->db->where('DATE(assigned_result.date) >=', $start_date);
    $this->db->where('DATE(assigned_result.date) <=', $end_date);

    $this->db->group_by('assigned_result.user_id');
    $query2 = $this->db->get('assigned_result')->result();

the second query result looks like this

Name    Date          winning
Ned     2019-07-26      120

Now i want to combine the queries like this

Name    Date          Purchase   winning 
Ned     2019-07-26      210.60         120

For getting this result i had joined the queries and return like this

$this->db->select('bills.date as d_date,bill_details.agent_name,
   SUM(bill_details.profit) AS total_profit,SUM(assigned_result.total_price) AS t_price,SUM(assigned_result.total_dc) AS t_dc');
   $this->db->join('bill_details', 'bill_details.bill_id=bills.id','left'); 
   $this->db->join('assigned_result', 'bills.id=assigned_result.bill_no', 'left');
   $this->db->where('DATE(bills.date) >=', $start_date);
   $this->db->where('DATE(bills.date) <=', $end_date); 
   $this->db->group_by('Date(bills.date)');
   $this->db->group_by('bills.customerid');
   $query1 = $this->db->get('bills')->result();
   return $query1;

but am getting result like this

Name    Date          Purchase  winning
Ned     2019-07-26      226      160

purchase amount is getting wrongly.

The problem is the joining of assigned_result table. An extra value is getting in total_profit while joining — user_777, Sep 10 '19 at 14:20
It seems you have a mistake in first sql query, here `$this->db->join('bills', 'bill_details.bill_id=bills.id','left'); `. Otherwise, I could help you to make the last query resultable. — Aksen P, Sep 11 '19 at 11:47
show the first table rows `where Name = 'Ned'`, without `SUM()`, do it in your database sql editor. The second step - check your `join`, because you're joining `bills` with `bills`, you see? The third step - you're showing the second query result not according to your `select`'ed columns as you've wrote in query. — Aksen P, Sep 12 '19 at 18:30
no am not joining `bills` with `bills` table.am joining `bills` with `bill_details` and `assigned_result` table — user_777, Sep 13 '19 at 05:01
check your code near `$query1`... Otherwise, do what I ask, cause it's to hard identify what means `t_price` or `t_dc` columns aliases according to your result view... you're joining `$this->db->join('` **bills** `', 'bill_details.bill_id=bills.id','left');` and after doing `$this->db->get('` **bills** `')->result();`. — Aksen P, Sep 13 '19 at 05:34
there is no line `$this->db->join(' bills ', 'bill_details.bill_id=bills.id','left');` like this in my query — user_777, Sep 13 '19 at 06:01
[img1](https://imgur.com/MJH3F8V) and [img2](https://imgur.com/NW7xFVk). And now you will say to me the same? Your data is unclear. Please, make columns more understandable. I mean - what column of these are equal to `winning`? — Aksen P, Sep 13 '19 at 08:21
sorry that was mistakenly typed and changed that code..Now can you please check that — user_777, Sep 13 '19 at 08:28

Aksen P · Answer 1 · 2019-09-13T11:39:02.947

Accroding to your SQL Editor data next query is for you:

select 
   B.user_id, 
   B.agent_name, 
   sum(B.profit),
   C2.tprice, 
   C2.tdc 
from B 
      join A on A.id = B.bill_id and A.customerid = B.user_id
 left join ( select 
                  C.user_id, 
                  C.data_id, 
                  C.bill_no, 
                  sum(C.total_price) tprice, 
                  sum(C.total_dc) tdc from C) C2 on C2.data_id = B.id and C2.user_id = B.user_id 
group by B.user_id;

PHP: (reference)

    $this->db->select('DATE(bills.date) as d_date,
                      bill_details.user_id,
                      bill_details.agent_name,
                      SUM(bill_details.profit) AS total_profit,
                      C2.tprice AS t_price,
                      C2.tdc AS t_dc');

   $this->db->join('bills', 'bills.id = bill_details.bill_id and bills.customerid = bill_details.user_id','left');
   $this->db->join('(select 
                assigned_result.user_id, 
                assigned_result.data_id, 
                assigned_result.bill_no, 
                sum(assigned_result.total_price) tprice, 
                sum(assigned_result.total_dc) tdc from assigned_result) as C2','C2.data_id = bill_details.id and C2.user_id = bill_details.user_id', 'LEFT'); 

   $this->db->where('DATE(bills.date) >=', $start_date);
   $this->db->where('DATE(bills.date) <=', $end_date); 

   $this->db->group_by('bill_details.user_id)'); 

   $query1 = $this->db->get('bill_details')->result();
   return $query1;

or

    $this->db->select('assigned_result.user_id, 
                assigned_result.data_id, 
                assigned_result.bill_no, 
                sum(assigned_result.total_price) tprice, 
                sum(assigned_result.total_dc) tdc')
            ->from('assigned_result');   

    $subquery = $this->db->_compile_select();
    $this->db->_reset_select();  

    $this->db->select('DATE(bills.date) as d_date,
                      bill_details.user_id,
                      bill_details.agent_name,
                      SUM(bill_details.profit) AS total_profit,
                      C2.tprice AS t_price,
                      C2.tdc AS t_dc');

   $this->db->join('bills', 'bills.id = bill_details.bill_id and bills.customerid = bill_details.user_id','left');
   $this->db->join("($subquery) C2",'C2.data_id = bill_details.id and C2.user_id = bill_details.user_id', 'LEFT'); 

   $this->db->where('DATE(bills.date) >=', $start_date);
   $this->db->where('DATE(bills.date) <=', $end_date); 

   $this->db->group_by('bill_details.user_id)'); 

   $query1 = $this->db->get('bill_details')->result();
   return $query1;

No still i got same result..here is my data collection and i changed table names `bills ` as `A` and `bill_details` as `B` and `assigned_result` as `C`http://sqlfiddle.com/#!9/c85a910/3 — user_777, Sep 13 '19 at 10:38
@user_777, http://sqlfiddle.com/#!9/c85a910/39 Now I'll help you to make a new php query. — Aksen P, Sep 13 '19 at 11:16
thankyou for helping in your first query i got the `total_profit` value but the `t_price` and `t_dc` gets null — user_777, Sep 13 '19 at 12:02
@user_777, try to find how to make a subquery, you need to create `C2 table` for joining, as you see in `SQL Editor`, read the reference and try to get it. You see that it depends of CodeIgniter version and core files. — Aksen P, Sep 13 '19 at 12:09

how can we join this two table results in to one

1 Answers1