Jump to content


Photo

MySQL DISTINCT Issues


  • Please log in to reply
7 replies to this topic

#1 High_-_Tek

High_-_Tek
  • Members
  • PipPipPip
  • Advanced Member
  • 72 posts

Posted 20 March 2006 - 08:44 PM

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

$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'];
}

Thanks for any help

PHPFreak is my homie!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 March 2006 - 09:47 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 20 March 2006 - 10:43 PM

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:

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'

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

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'


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 March 2006 - 11:10 PM

And wickning1 proves, yet again, why multi-table select syntax is useless and evil.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 High_-_Tek

High_-_Tek
  • Members
  • PipPipPip
  • Advanced Member
  • 72 posts

Posted 20 March 2006 - 11:17 PM

Hey Guys!

Thanks for the help :)

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

#6 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 20 March 2006 - 11:38 PM

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:

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

Why doesn't that work?

#7 High_-_Tek

High_-_Tek
  • Members
  • PipPipPip
  • Advanced Member
  • 72 posts

Posted 20 March 2006 - 11:50 PM

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 :)

#8 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 20 March 2006 - 11:52 PM

I modified my earlier reply.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users