Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: A little lost with this query

    Hi Folks...

    I am having a little problem with a certain type of query. Allow me to explain. Let' say I have these two tables:

    booking table


    | idbooking | FK_date | bookingstarttime | bookingendtime |

    *primary key is 'idbooking'
    *foreign key is 'FK_date' , happens to be the primary of the 'calendar' table.


    calendar table
    | date | day |

    *primary key is 'date'

    I am able to do a query that displays the date/day in which there are bookings, however what I really need is the reverse, i.e, the date/day in which there are NO bookings. The query I wrote to display the date/day in which there are bookings is as follows:

    Code:
    select date, day
    from calendar
    where date IN(
      select FK_date
      from booking
           );
    This works fine, but how do I do the reverse? I tried the following, but none gave the expected result.

    Code:
    select date,day
    from calendar
    where date IN !=
    (select FK_date
    from booking);
    Code:
    select date,day
    from calendar
    where date IN (
    select date from booking, calendar
    where booking.FK_date != calendar.date);
    I hope someone can help/advise me. Thanks.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You were close, try :

    Code:
    select date, day
    from   calendar
    where  date NOT IN (
               select FK_date
               from   booking );

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    Hi Mike...

    Thank you for your prompt reply and the correct solution. It just didn't occur to me to use the NOT logical operator. I tried your solution and it worked. I appreciate it, thanks once again.

    regards
    glisando

Posting Permissions

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