Results 1 to 2 of 2
Oct 11, 2009, 13:50 #1
- Join Date
- Sep 2006
- 0 Post(s)
- 0 Thread(s)
Would a different JOIN be better for this? Or something else?
Hello All! I'm trying to execute a query and am having trouble getting the results I was hoping for....
I'm gonna simplify what I have to hopefully make it easier for you guys to help me with a solution.
customers & appointments
each customer can have many appointments but an appointment can only have 1 customer.
ok, so I'm trying to display all the appointments but do not want to show duplicates of customers.
For instance, if 'Jon' has 3 appointments at varying times in the future, I only want to show his closest appointment.
Currently I'm using this JOIN
INNER JOIN appointments ON appointments.customerID = customer.id ";
I'm coding in php so I guess it could be done there, but just seems it would be more efficient to do it in the query if possible.
Thanks in advance for any guidance or assistance you may be able to provide!
**I should note that I'm still just getting my feet wet with JOINs so I apologize if this is a dumb question. Thanks!
Oct 11, 2009, 14:26 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 63 Post(s)
- 3 Thread(s)
it's not a dumb question
"closest appointment per customer" is the "row with groupwise max for some column" problem
here are a number of ways to write the SQL -- http://jan.kneschke.de/projects/mysql/groupwise-max/
try it on your customers and appointments and show us the query if it doesn't work