Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Converting subqueries into JOIN ON

 
Mike Curwen
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Dana Hanna
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic