Join Question

Results 1 to 2 of 2

Thread: Join Question

  1. #1
    Join Date
    Dec 1969

    Default Join Question

    I am working on a website for a salon that will allow customers to schedule appointments online with a chosen stylist if that stylist does not already have an appointment at that time.<BR><BR>I have 1 table to keep track of all the appointments (tblCalendar) and 1 that stores the information of the stylists.<BR>Here are their simplified structures:<BR>tblCalendar:<BR>StartDate - datetime<BR>EndDate - datetime<BR>StylistID - int<BR><BR>tblStylist<BR>ID - int<BR>Name -varchar<BR><BR>When a customer is going through the scheduling process they select a date and time for their appointment and the next page is supposed to list all the stylists that do not have anything scheduled at that time. So far the sql query that i have been working on is this:<BR>select, from tblStylist s, tblCalendar c where &#060;&#062; c.StylistID and (&#039;3/23/2002 11:30:00 AM&#039; between c.startdate and c.enddate) order by<BR><BR>this works great if there is only 1 row in the calendar table starting on the same day and same time... if another person were to try to schedule an appointment on 3/23 starting at 11:30 the dropdown box would contain duplicates of all the name, with the exception of the stylist assigned to the other appointment in which case their name would only show up once. Adding distinct to the query will get rid of the duplicates but the stylist with the scheduled appointment will also still be in the list. <BR>any suggestions would be greatly appreciated<BR><BR><BR>-scott

  2. #2
    Join Date
    Dec 1969

    Default RE: Join Question

    Think about it a second:<BR><BR>If there are no rows in the calendar table at the given time, then all stylists appear.<BR><BR>If there is one row (one stylist) at that time, then everybody else appears.<BR><BR>But if there are two or more rows, then there is *NO STYLIST* who matches *all* the appointments, so of course they all show up! <BR><BR>The duplicates issue is a red herring. Make an appointment for *every* stylist in the same timeslot and see what you get!<BR><BR>SO...<BR><BR>We don&#039;t want a JOIN! It&#039;s not the right structure for this.<BR><BR>We want a sub-select, thus:<BR><BR>select id, name <BR>from tblStylist<BR>where id NOT IN (<BR>&nbsp; &nbsp; SELECT StylistID FROM tblCalendar <BR>&nbsp; &nbsp; WHERE &#039;3/23/2002 11:30:00 AM&#039; between c.startdate and c.enddate )<BR>order by name<BR><BR>See if that works.<BR><BR>

Posting Permissions

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