Combining 2 Mysql query results

[sandysmith]

Hi All,

 

Below is my code to add the two different query results into one. This is working fine when both the queries have same no.of rows.

 

eg: row1 = 1 2 3 (Query1)

 

      row2 = 3 5 5 (Query2)

 

        o/p:    4 7 8

 

Let's say, I have few rows which are not exactly matched with first query.

 

eg: row1 = 1    2      3      2  (Query1)

 

      row2 = 3 empty empty  5  (Query2)

 

o/p :          4      2      3      7  (I want the o/p to be like this)

 

empty means there is no data from the second query.

 

In my while, && working fine when the 2 queries have same no.of rows.

 

 

while (($row1 = mysql_fetch_assoc($rs1)) && ($row2 = mysql_fetch_assoc($rs2)))

{

 

 

$strHtml .= "<tr>";

 

$strHtml .= "<td align=center colspan=3>".($row1['Calls']+$row2['Calls'])."</td>";

$strHtml .= "<td align=center colspan=3>".($row1['actual_duration(min)A']+$row2['actual_duration(min)A'])."</td>";

$strHtml .= "<td align=center colspan=3>".($row1['call_usage']+$row2['call_usage'])."</td>";

$strHtml .= "<td align=center colspan=3>".($row1['disconnection_charge']+$row2['disconnection_charge'])."</td>";

$strHtml .= "<td align=center colspan=3>".($row1['total_revenue']+$row2['total_revenue'])."</td>";

 

$strHtml .= "</tr>";

 

}

 

Is the while loop i am using correct or there is any other better solution for this?

 

please help me, Thanks in advance.

[MasterACE14]

you need to adjust your query, not your code. Should only need one mysql_fetch_assoc()

[sandysmith]

Thank you for the quick reply. But, can u tell me how do i do that?

[MasterACE14]

can you post your 2 queries?

[sandysmith]

The first query is:

select a.tu_call_date, sum(a.calls) as Calls, round(a.actual_duration,2) as `actual_duration(min)A`, round(b.mrcost,2) as mrcost,

from

(select tu_call_date, sum(tu_no_of_call) as calls, sum(tu_usage_usd) as call_usage,

from db.zzz

group by tu_call_date) a

inner join

(select call_date, sum(mrcost) as mrcost, sum(duration) as actual_duration

from db.xxx inner join yyy

on partner_code = tp_partner_code

group by call_date, tp_account_group) b

on a.tu_account_group = b.tp_account_group

and a.tu_call_date = b.call_date

where a.tu_account_group = 'aaa'

group by a.tu_call_date

order by a.tu_call_date

 

and the second query is

 

select a.tu_call_date, sum(a.calls) as Calls, (round(a.actual_duration,2)) as `actual_duration(min)A`, round(b.mrcost,2) as mrcost,

from

(select tu_call_date, sum(tu_no_of_call) as calls, sum(tu_usage_usd) as call_usage

from zzz

group by tu_call_date) a

inner join

(select call_date, sum(mrcost) as mrcost, sum(duration) as actual_duration

from kkk inner join yyy

on partner_code = tp_partner_code

where and partner_code in ('a', 'b')

and destination_id in

(

select destination_no from abc

)

group by call_date, tp_account_group) b

on a.tu_account_group = b.tp_account_group

and a.tu_call_date = b.call_date

where a.tu_account_group = 'bbb'

group by a.tu_call_date

 

 

[sandysmith]

Hi All,

 

Any help on the above question plz.