Mysql重复结果

Question

I'm making a invoice script but my query is returning al my entries mulpiple times when i fetch them.

this is my code:

$query      =   "   SELECT d.Quantity, d.ProductID, p.ProductName, d.UnitPrice, d.Discount
                            FROM customers AS c, orders AS o, order_details AS d, products AS p
                            WHERE o.OrderID = '10248'
                            AND o.OrderID = d.OrderID
                            AND d.ProductID = p.ProductID
                        ";

        $result     =   mysql_query($query);

        $table      =   '';

        while($row  =   mysql_fetch_assoc($result)){
            $table  .=  '<tr>';
            $table  .=  '<td>' . $row['Quantity'] . '</td>';
            $table  .=  '<td>' . $row['ProductID'] . '</td>';
            $table  .=  '<td>' . $row['ProductName'] . '</td>';
            $table  .=  '<td>' . $row['UnitPrice'] . '</td>';
            $table  .=  '<td>' . $row['Discount'] . '</td>';
            $table  .=  '<td>' . (100 - $row['Discount']) / 100 * $row['UnitPrice'] . '</td>';
            $table  .=  '</tr>';
        }

this is, a part, of what it returns.

Quantity    ProductID   ProductName     UnitPrice   Discount    Subtotal
12  11  Queso Cabrales  14.0000 0   14
10  42  Singaporean Hokkien Fried Mee   9.8000  0   9.8
5   72  Mozzarella di Giovanni  34.8000 0   34.8
12  11  Queso Cabrales  14.0000 0   14
10  42  Singaporean Hokkien Fried Mee   9.8000  0   9.8
5   72  Mozzarella di Giovanni  34.8000 0   34.8
12  11  Queso Cabrales  14.0000 0   14
10  42  Singaporean Hokkien Fried Mee   9.8000  0   9.8
5   72  Mozzarella di Giovanni  34.8000 0   34.8
12  11  Queso Cabrales  14.0000 0   14
10  42  Singaporean Hokkien Fried Mee   9.8000  0   9.8
5   72  Mozzarella di Giovanni  34.8000 0   34.8

while it sould only return 3 entries.

anny toughts?

Answer 1

You have a Cartesian join:

SELECT d.Quantity, 
       d.ProductID, 
       p.ProductName, 
       d.UnitPrice, 
       d.Discount
FROM   **customers AS c,** 
       orders AS o, 
       order_details AS d, 
       products AS p
WHERE  o.OrderID = '10248'
AND    o.OrderID = d.OrderID
AND    d.ProductID = p.ProductID

You need to include a join to customers, or drop that table from the SQL.