A little lost with this query

glisando · #1 (**permalink**) 03-13-10, 05:25

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.

mike_bike_kite · #2 (**permalink**) 03-13-10, 05:31

You were close, try :

Code:

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

glisando · #3 (**permalink**) 03-13-10, 05:43

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

Internet Services

Web Development

Digital Marketing

Consumer Tech