SQL Query Help

Dev Shed Forums Sponsor:

June 21st, 2012, 09:34 AM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation 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!

June 21st, 2012, 10:20 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,349

Time spent in forums: 1 Month 4 Days 7 h 10 m 28 sec
Reputation Power: 390

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.

June 21st, 2012, 03:26 PM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation 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?

June 25th, 2012, 09:47 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,349

Time spent in forums: 1 Month 4 Days 7 h 10 m 28 sec
Reputation Power: 390

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

June 25th, 2012, 10:31 AM

kloud

Registered User

Join Date: Jun 2012

Posts: 15

Time spent in forums: 2 h 56 m 48 sec
Reputation Power: 0

Thanks SB.

That's perfect. Much appreciated.

Developer Shed Advertisers and Affiliates