Thread: SQL Query Help

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0

    SQL Query Help


    Hi,

    I have 2 tables. Attendant and AttendanceRecord. I want to log attendance in the attendance record table (obviously!).

    I want to present the user with a dataset which has the name of the attendant and a flag as to whether they have attended on a particular date or not. So the user will set a date in control and the query will return a set of all attendees and whether they attended or not.

    Have tried a number of different queries and getting close but nothing definitive.

    Help!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    Code:
    select Attendant.name,
           case when AttendanceRecord.AttendanceDate = <given date> then 'Present' else 'Not present' end
      from attendant
      left
      join AttendanceRecord
        on attendant.pk = AttendanceRecord.fk
       and AttendanceRecord.AttendanceDate = <given date>
    Change column names as apropriate.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0
    Thanks a million - this does the trick.

    Only thing is that in order to fit it into the tool I'm using the join isn't the easiest thing to use. Just wondering if there's any other way of doing this other than using a join on the date - is it possible to do using a where clause?

    Why is the join necessary?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    Code:
    select Attendant.name,
           case when exists(select * 
                              from AttendanceRecord
                             where AttendanceDate = <given date>
                               and attendant.pk = AttendanceRecord.fk) then 
              'Present' else 'Not present' end
      from attendant
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    15
    Rep Power
    0
    Thanks SB.

    That's perfect. Much appreciated.

IMN logo majestic logo threadwatch logo seochat tools logo