1 Query instead of Multiple Queries

SickDwarf

Hi people.

I currently have a PHP script, that retrieves certain numbers from multiple tables in a db, and then adds/substracts these numbers to present a final number.

I have the following SQL queries to get the numbers:

$sql_1 = "SELECT amount_given FROM esh_deposits WHERE user_id = ".$user_id; 
$sql_2 = "SELECT amount_given FROM esh_compounds WHERE user_id = ".$user_id.;
$sql_3 = "SELECT amount FROM esh_adjustments WHERE user_id = ".$user_id." AND add_substract = 0";
$sql_4 = "SELECT amount_given, amount_taken FROM esh_withdrawals WHERE user_id = ".$user_id;
$sql_5 = "SELECT amount FROM esh_adjustments WHERE user_id = ".$user_id." AND add_substract = 1";

The returned values from $sql_1, $sql_2, $sql_3 are added to $total_balance. After that, the returned values of $sql_4 and $sql_5 are substracted from $total_balance.

I am trying to convert this to 1 mysql query, but I just can't get the required result. Can anyone help me on this?

stick_figure

I guess I'd do something like this:

$sql_1 = 'SELECT (SELECT sum(amount)
    (SELECT amount_given AS amount FROM esh_deposits WHERE user_id = '.$user_id.') UNION
    (SELECT amount_given FROM esh_compounds WHERE user_id = '.$user_id.') UNION
    (SELECT amount FROM esh_adjustments WHERE user_id = '.$user_id.' AND add_substract = 0)
) - (SELECT sum(amount)
    (SELECT amount_given AS amount FROM esh_withdrawals WHERE user_id = '.$user_id.') UNION
    (SELECT amount FROM esh_adjustments WHERE user_id = '.$user_id.' AND add_substract = 1)
) AS total_balance;

I didn't know where you wanted the amount_taken value, but you get the point. I think that should work about right...

mystrymaster

You can replace the first 4 with one query

$sql = "SELECT d.amount_given, c.amount_given, a.amount, w.amount_given, w.amount_taken 
FROM `esh_deposits` as `d` , `esh_compounds` as `c` , `esh_adjustments` as `a` , `esh_withdrawals as `w`
WHERE d.user_id = '$user_id' AND c.user_id = d.user_id AND a.user_id = d.user_id AND w.user_id = a.user_id AND a.add_substract = '0'";

You can read up on some more complicated joins and such but this should dive you the desired result from this query and just work with the data from this query to get the totals you need