如何从另一个表获取我的WHERE子句的特定ID？ 使用连接

Question

i use this table, and i was computing for the total of the SUM(quantity * price) but now i dont know how to use it for the specific serial from the customers table.

here is my query

<?php 

$qry = "SELECT SUM(price * quantity), chever from order_detail";

$result = @mysql_query($qry);
while ($row=mysql_fetch_array($result)){
echo $row['chever'];
}
?>

i was using this but i didnt really understand it, i created the new query because SUM() messes up the old one, i dont kno why, but it shows 1 row only and no more.

$qry = "SELECT
            order_detail.quantity,
            order_detail.price,
            customers.serial,
            customers.name,
            customers.payment,
            customers.carrier, 
            orders.date, 
            order_detail.productid,
            order_detail.quantity, 
            order_detail.price, 
            order_detail.orderid, 
            inventory.prod_name
        FROM 
            customers 
        RIGHT JOIN 
            orders on customers.serial=orders.serial 
        RIGHT JOIN 
            order_detail on orders.serial=order_detail.orderid 
        LEFT JOIN 
            inventory on order_detail.productid=inventory.prod_id 
        WHERE
            customers.serial='{$_GET['serial']}'";

mysql_set_charset("UTF8");
$result = @mysql_query($qry);
if($result === FALSE) {
    die(mysql_error()); // TODO: better error handling
}
echo "<div align='left'>";
echo "<table class='CSSTableGenerator' id=''>
<tr>


<td>Products</td>
<td>Quantity</td>
<td>Total Price</td>


<tr>";
while ($row=mysql_fetch_array($result)){
echo "<tr>";
//echo "<td>".$row['date']."</td>";
//echo "<td>".$row['name']."</td>";
//echo "<td>".$row['orderid']."</a></td>";
echo "<td>".$row['prod_name']."</td>";
echo "<td>".$row['quantity']."</td>";
echo "<td>".($row['price']*$row['quantity'])."</td>";
//echo "<td>".$row['payment']."</td>"
echo "</tr>";
    }
echo "</table>";

how do i join my tables and use the where clause.

Answer 1

This might do the trick

 $qry = "SELECT SUM(order_detail.price * order_detail.quantity) AS totalOD, customers.serial from order_detail
            LEFT JOIN orders ON orders.serial = order_detail.orderid
            RIGHT JOIN customers ON customers.serial = orders.serial
            WHERE
                customers.serial='{$_GET['serial']}'";


    $result = @mysql_query($qry);
    while ($row=mysql_fetch_array($result)){
    echo $row['totalOD'];
    }
    ?>