Converting subqueries into JOIN ON

[Mike Curwen]

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                  Purchases
-------------------       ---------------------------------------------------------
id   F_Name   L_Name      id   category_FK  customer_FK   payment_FK   amount

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.

SELECT p.id, p.category_FK, p.payment_FK, p.amount, c.F_Name, c.L_Name 
FROM Customers as c, Purchases as p
WHERE  p.category_FK=X 
AND    p.customer_FK=c.id
AND    p.payment_FK=Y
AND    p.amount=  (
     SELECT MAX(p2.amount) 
     FROM Purchases p2
     WHERE p.category_FK = p2.category_FK
     AND   p.payment_FK = p2.payment_FK
)

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 ]

[Dana Hanna]

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...

[Mike Curwen]

Hi Dana,

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.

Back to SQL 101 for me!!