MySQL DISTINCT Issues

[High_-_Tek]

Hey All,

I have this query which sets up a table of Invoice Numbers and Sub Totals, but even when I use the DISTINCT command, I get duplicates

I believe this is because I get the Invoice_# pulled from the Order_Details table (which is boudn to hold doubles because it containts the product ids). But when I cut that part out and only have it 'WHERE' info from the Order table it repeats every single Invoice_Number in a loop :(

FOr example I have two entries for invoice 3 that Im looking at

[code]
$month=$_SESSION['month'];// Old Way of Doing it

$custid=$_SESSION['custid'];
$year=$_SESSION['year'];
//
$sql_orders=mysql_query("SELECT DISTINCT Orders.Invoice_Number AS InvoiceID, Orders.Subtotal AS sub, categories.catname AS CatName FROM Invoice_Record AS ir, Orders, Order_Details, Customers,Products, categories WHERE Orders.Customer_ID='$custid' AND Orders.month='$month' AND ir.month='$month' AND Orders.year='$year' AND Orders.Customer_ID=Customers.customerid AND categories.catid=Products.Product_Cat_ID AND Order_Details.Product_ID=Products.Product_ID AND Orders.Invoice_Number=Order_Details.Invoice_Number") OR DIE (mysql_error());
//


...

while ($order=mysql_fetch_array($sql_orders)){
  //$total=$order['price'] * $order['quan'];
  //$subtotal+=$total;
  //$invoice=$order['InvoiceID'];
  echo "<tr><td><a href=\"invoice3.php?custid=$custid&orderid={$order['InvoiceID']}\">{$order['InvoiceID']}</a><td>£{$order['sub']}</td></tr>";
  $subtotal+=$order['sub'];
}
[/code]

Thanks for any help

PHPFreak is my homie!

[fenway]

Two things -- first, DISTINCT will only return distinct results if ALL the columns requested are indentical. Second, sounds like you need a GROUP BY clause on Invoice_Number.

[wickning1]

You don't need DISTINCT or GROUP BY. You're just missing a join condition for Invoice_Record. It doesn't have any link into the rest of the tables. If you use the INNER JOIN ... ON syntax, this becomes painfully obvious:

[code]SELECT o.Invoice_Number AS InvoiceID, o.Subtotal AS sub, c.catname AS CatName
FROM Invoice_Record ir
INNER JOIN Orders o ON ???????????
INNER JOIN Customers cu ON cu.customerid=o.Customer_ID
INNER JOIN Order_Details d ON d.Invoice_Number=o.Invoice_Number
INNER JOIN Products p ON p.Product_ID=d.Product_ID
INNER JOIN categories c ON c.catid=p.Product_Cat_ID
WHERE o.Customer_ID='$custid' AND o.month='$month' AND ir.month='$month' AND o.year='$year'[/code]

Here's my final cleaned up version of your query, guessing that Invoice_Record has an Invoice_Number column:

[code]SELECT o.Invoice_Number AS InvoiceID, o.Subtotal AS sub, c.catname AS CatName
FROM Invoice_Record ir
INNER JOIN Orders o ON o.Invoice_Number=ir.Invoice_Number
INNER JOIN Customers cu ON cu.customerid=o.Customer_ID
INNER JOIN Order_Details d ON d.Invoice_Number=o.Invoice_Number
INNER JOIN Products p ON p.Product_ID=d.Product_ID
INNER JOIN categories c ON c.catid=p.Product_Cat_ID
WHERE o.Customer_ID='$custid' AND o.month='$month' AND o.year='$year'[/code]

[fenway]

And wickning1 proves, yet again, why multi-table select syntax is useless and evil.

[High_-_Tek]

Hey Guys!

Thanks for the help :)

But now I get random duplication ... some have 6 copies, some have 2, one has 1 copy

[wickning1]

Well, I really just fixed the errors in your query, but I can't figure out exactly what data you are wanting to be returned. Maybe you should explain in more detail.

From your code it looks like all you need is a single table query:

[code]SELECT Invoice_Number AS InvoiceID, Subtotal AS sub
FROM Orders WHERE Customer_ID='$custid' AND month='$month' AND year='$year'[/code]

Why doesn't that work?

[High_-_Tek]

Ok,

I am trying to list the Invoice Number and a link to that invoice record as well as a subtotal next to it in the next table

Thanks :)

[wickning1]

I modified my earlier reply.