Hi everyone

I'm gradually building an intranet DB application to record a publishing
company advertising bookings.

I'm trying to do a query which will return the adbookings that are in a
particular selection of publications, and not in a selection of others.

There are 3 tables involved here...
adbookings ...details of the adbookings
adinserts ...link between adbookings and publications
publications ...details of the publications (publication name, deadline
date etc)

When you create an adbooking you select several publications that the
advert will appear in... this is stored in the adinserts table.
The adinserts table contains the link between the adbooking, and the
names of the publications the advert is selected to appear in.
So there are 2 fields in the adinserts table... booking_id and
publication_id


Here's my query:
I'm sure it can be massively improved to be made more efficient but I
want to just get it working first then experiment.

SELECT DISTINCT `adbookings`.`booking_id`, `adbookings`.*
FROM `adbookings`
LEFT JOIN `adinserts` USING(`booking_id`)
LEFT JOIN `publications` ON
`adinserts`.`publication_id`=`publications`.`publi cation_id`
WHERE `adinserts`.`publication_id` IN (120) AND
`adinserts`.`publication_id` NOT IN (124)
ORDER BY `adbookings`.`backup_company_name`
LIMIT 0, 1000

What I'm trying to do is return all the adbookings that have adinsert
records with a publication_id of 120, and without adinsert records with
publication_id of 124.


The bit that's causing the problem is WHERE ... IN () AND ... NOT IN ()
section.
Here's what I've tried so far:

1) If I try the following...
WHERE `adinserts`.`publication_id` IN (120)
.... gives me 45 results... correct. There are 45 bookings that should
appear in publication #120.

2) Then the following...
WHERE `adinserts`.`publication_id`IN (124)
.... gives me 31 results... correct. There are 31 bookings that should
appear in publication #124

3) And now when I try this (same as full query above)...
WHERE `adinserts`.`publication_id`IN (120) AND
`adinserts`.`publication_id` NOT IN (124)
.... gives me 45 results... incorrect. It returns the same result set as
#1 above. It should actually return about 16 results


Is it acceptable to do WHERE ... IN () AND ... NOT IN ()?
Or do I need to re-write my query to use sub-queries?


I'm using IN () because those sections will eventually be a
comma-separated list of up to 10 publication IDs.

Any ideas / suggestions?


Thanks
Ben