SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    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.

    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

    Code:
    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •