mysql: list of rows in table that aren't referenced in another.

Question

Hi There,

I have two tables, timetable and lesson_booking these are linked via timetable_id.

timetable represents timetable entries for a given day (mon, tue etc) that can be selected and used to make a booking for a client in lesson_booking

What I would like to do is get a list of timetable entries that have no bookings associated with it.( i.e. find empty timtables slots)

I can do this but my problem is with doing it for a certain date. I am having some trouble with this as the date is in the lesson_booking table and the entries I am requesting have no link to lesson_booking

Here is what I have so far: The following will return all empty timetable entries. I want to do it for a specified date only.

I have a feeling that I need to modify the left join but i'm unsure how.

    SELECT 
        lesson_booking.booking_date,
        employee.employee_firstname, employee_lastname,
        TIME_FORMAT(start_time, '%l:%i %p') AS start_time,
        TIME_FORMAT(end_time, '%l:%i %p') AS end_time,
        lesson_type.lesson_type_name

FROM timetable
        LEFT JOIN lesson_booking ON lesson_booking.timetable_id = timetable.timetable_id
        JOIN employee ON timetable.employee_id = employee.employee_id
        JOIN lesson_type ON timetable.lesson_type_id = lesson_type.lesson_type_id
        JOIN day_name ON day_name.day_name_id = timetable.day_name_id
WHERE   ISNULL(lesson_booking_id)
    AND
    day_name = DATE_FORMAT('2010-7-5', '%W')

NOTE: there are other tables linked for employee, lesson type etc. Also, day_name is a table for days i.e monday,tuesday, wednesday etc. The where clause narows to display only timteable entries of a specified day.

Any help is greatly appreciated..

Thanks.

UPDATE:

Note: Here are twi queries. The first contains all the information I want but some extra too.. The second contains the information that I want exluded from the first. How do I combine the two to get just the data I want? Thanks.

    SELECT
    timetable.*
FROM
    timetable
    JOIN day_name ON day_name.day_name_id = timetable.day_name_id
WHERE
    day_name = DATE_FORMAT('2010-7-5', '%W');

-- exclude the following result from the above result.
SELECT
    timetable.*
FROM
    lesson_booking
    JOIN timetable ON timetable.timetable_id = lesson_booking.timetable_id
WHERE
    booking_date = '2010-07-05';

definitions for lesson_booking and timetable

    /*
SQLyog Community- MySQL GUI v8.22 
MySQL - 5.1.30-community-log 
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;

create table `COLUMNS` (
    `Field` varchar (192),
    `Type` blob ,
    `Null` varchar (9),
    `Key` varchar (9),
    `Default` blob ,
    `Extra` varchar (81)
); 
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('lesson_booking_id','int(11)','NO','PRI',NULL,'auto_increment');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('client_id','int(11)','NO','MUL',NULL,'');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('timetable_id','int(11)','NO','MUL',NULL,'');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('horse_owned_id','int(11)','NO','MUL',NULL,'');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('transaction_id','int(11)','YES','MUL',NULL,'');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('booking_date','date','NO','',NULL,'');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('cancelled_status','int(11)','YES','MUL',NULL,'');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('no_show','tinyint(1)','NO','','0','');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('same_time_next_week_booking_id','int(11)','YES','MUL',NULL,'');

/*
SQLyog Community- MySQL GUI v8.22 
MySQL - 5.1.30-community-log 
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;

create table `COLUMNS` (
    `Field` varchar (192),
    `Type` blob ,
    `Null` varchar (9),
    `Key` varchar (9),
    `Default` blob ,
    `Extra` varchar (81)
); 
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('timetable_id','int(11)','NO','PRI',NULL,'auto_increment');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('day_name_id','int(11)','NO','MUL',NULL,'');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('start_time','time','NO','',NULL,'');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('end_time','time','NO','',NULL,'');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('lesson_type_id','int(11)','NO','MUL',NULL,'');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('employee_id','int(11)','NO','MUL','0','');
insert into `COLUMNS` (`Field`, `Type`, `Null`, `Key`, `Default`, `Extra`) values('timetable_active_status','tinyint(1)','NO','','1','');

Answer 1

I think I was right about your problem. You want to find the dates where no lessons are booked, yes? But the only dates that appear in the database are those when there are bookings; that's because the date goes with the booking not the lesson. I think you need another table, perhaps something like 'available_lessons' that expands the timetabled lessons into actual lessons; one row in the timetable says, 9am Thursday Morning, and this would be expanded in the available_lessons to one row for 9am 8th July, another row for 9am 15th July, and so on, filled in as far ahead as necessary. Bookings could then be attached to these rows and the dates where no bookings were present found easily enough.

Not a quick or easy solution, sadly.

Answer 2

Use:

   SELECT tt.*
     FROM TIMETABLE tt
     JOIN day_name dn ON dn.day_name_id = ttt.day_name_id
                     AND dn.day_name = DATE_FORMAT('2010-7-5', '%W');
LEFT JOIN LESSON_BOOKING lb ON lb.timetable_id = tt.timetable_id
                           AND lb.booking_date = '2010-07-05'
    WHERE lb.lesson_booking_id IS NULL