使用Union或Join在where语句中使用多个查询结果

Question

I need to combine results of 3 sum queries and echo the results in a loop. I am stuck on how this is done the right way. I would like to use a while loop to echo a row for each client that shows the results of the 3 sum queries grouped by the client name:

client name | Total Due | Total Due From Cash Sales | Total Due From Credit Sales

result shows all due to a client. noncashresult shows all due to a client from Credit sales. cashresult shows all due to a client from Cash sales.

$result = mysql_query("SELECT `client_name` As `Client`, SUM(`due_to_client`) As `Total Due` 
FROM `Consignment` 
WHERE `payout_approved` = 'Yes' 
GROUP BY `client_name`"); 

$noncashresult = mysql_query("SELECT `client_name` As `Client`, SUM(`due_to_client`) As `Credit Total Due`
FROM `Consignment` 
WHERE `payout_approved` = 'Yes' AND `paymenttype` IN ('Credit') 
GROUP BY `client_name`");

$cashresult = mysql_query("SELECT `client_name` As `Client`, SUM(`due_to_client`) As `Cash Total Due`
FROM `Consignment` 
WHERE `payout_approved` = 'Yes' AND `paymenttype` IN ('Cash') 
GROUP BY `client_name`");

while($row = mysql_fetch_array($result))
                    {
                echo $row['Client'];
                echo $row['Total Due'];
                echo $row['Credit Total Due'];
                echo $row['Cash Total Due'];
                    }

Answer 1

You could use something like:

SELECT `client_name` As `Client`,
SUM(`due_to_client`) As `Total Due`,
SUM(CASE WHEN `paymenttype`='Credit' THEN `due_to_client` ELSE 0 END) As `Credit Total Due`,
SUM(CASE WHEN `paymenttype`='Cash' THEN `due_to_client` ELSE 0 END) As `Cash Total Due`
FROM `Consignment` 
WHERE `payout_approved` = 'Yes' 
GROUP BY `client_name`

This should produce result:

Client | Total Due | Credit Total Due | Cash Total Due

Of course, try to avoid using mysql_* extension, while this was deprecated in mysql 5.5.x. Instead use mysqli or pdo extension