Would a different JOIN be better for this? Or something else?

[rustybuddy]

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.

2 tables

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 ";

This of course returns a list of all appts with many occurences of customers. What would be the best way to purge these duplicates out of my query results?

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!

[r937]

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