So I'm being frustrated by a mysql limitation, in that it does not support subqueries.
I can't believe that my query is THAT difficult to re-write, but nothing I come up with seems to work. It's either a syntax error or returns something spurious.
Here is my situation.
Two tables. Customer should be self-explained.
Purchases have an id, a category_FK which is the category of the item purchased, customer_FK is who purchased it, payment_FK is how (cash, cheque) and amount is the total amount of the bill.
I want to determine in each category, and for each payment type, who had the highest purchase amount.
X and Y are parametized above, because these values will change each time I run the query. Also, is this query even accurate? Give that I'm running mySQL (and cannot test it), I'm not even convinced *this* query returns what I think it should. And if it is accurate, is there a better query? More efficient?
As for converting this to a JOIN ON type single query:
I'd give you what I got so far, but I don't want to 'taint' anyone else. Can someone turn this into a INNER JOIN ON style single query? I'm sure I've missed something glaringly obvious. [ February 28, 2003: Message edited by: Mike Curwen ]
Joined: Feb 28, 2003
hey bud - you're making it too hard (i changed column names, you should have no problem seeing what I'm doing here though... SELECT p.id, p.category_id, p.payment_id, c.firstname, c.lastname, max(p.amount) as highest_amount FROM Customers as c, Purchases as p WHERE p.category_id=X AND p.payment_id=Y AND p.customer_id = c.id GROUP BY p.category_id, p.payment_id and FYI - MySQL supports subqueries in v 4.0+, but it's in beta. Subqueries are often used by beginners when other solutions exist...
I said or returns something spurious. and then further on I said I'm sure I've missed something glaringly obvious.
I came back here to post my own solution, and of course it's exactly what yours is.
My 'GROUP BY' clause didn't order on the two fields I was using as filters. (category and payment). This results in queries *sometimes* returning what you expect, until you change your data and "all of a sudden" it no longer works. It only looked like it ever worked, and all because I threw in 'GROUP BY p.id' to satisfy the parser because I was using a column function.