calculation that fetch value from different table

[huda89]

Hi,
Can i perform calculation that fetch value from different table in database?
i have two tables which are inbound and outbound table ,
i need to calculate item in stock,item out and total item.
i need to minus total item with item out,in order to find item in stock.
But the problem is total item is store in inbound table and item out is store in outbound table,
so how do i need to perform the calculation that fetch value from different table in database?

[Rabbit]

You can do that by joining the tables together. That's as much as I can say since I don't know anything about your table structures.

[huda89]

Hi Rabbit, thank you for your reply, I had done joining the table, but when I perform the calculation there's problem,I cannot get the value of $z, is there anything wrong with my code,thank you. Below is my code:

$query = "SELECT e.component_1, o.component_2, SUM(e.quantity_1), SUM(o.quantity) FROM equip_ments e LEFT JOIN outbound o ON e.serial_num=o.serial_num GROUP BY component_1";
 
$y = $row['SUM(e.quantity_1)'];
$x = $row['SUM(o.quantity)'];
$z = $row['SUM(e.quantity_1)'] - $row['SUM(o.quantity)'];
 

[zmbd]

We need to see the pertinent structure of the database and how the tables are related/joined .... something like:
TableName: tbl_example
[PK] autonumber primary key
[fieldone] Long foreign key to tbl_two 1:M
etc...

AND

Although one could assume that you're using MySQL as the database, it would be better if you would tell us which one you are using as there are some slight quirks between the various programs.

[Rabbit]

You should give your calculations aliases.

[huda89]

Hi Rabbit, it is means i should do the calculation something like this:
$z = $y - $x;

[Rabbit]

No, I mean that you should give your expressions in your SQL aliases.

select sum(someField) AS SumOfSomeField

[huda89]

Thanks, i just need to change code position.

$query = "SELECT e.component_1, o.component_2, SUM(e.quantity_1), SUM(o.quantity) FROM equip_ments e LEFT JOIN outbound o ON e.serial_num=o.serial_num GROUP BY component_1";
    $result = mysql_query($query) or die(mysql_error());
 
 
while($row = mysql_fetch_array($result)){
 
$y = $row['SUM(e.quantity_1)'];
$x = $row['SUM(o.quantity)'];
$z = ($row['SUM(e.quantity_1)'] - $row['SUM(o.quantity)']);
 
?>