>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`.`publ ication_id`
>WHERE `adinserts`.`publication_id` IN (120) AND
>`adinserts`.`publication_id` NOT IN (124)
>ORDER BY `adbookings`.`backup_company_name`
>LIMIT 0, 1000

Why are you using
WHERE adinserts.publication_id IN (120) AND adinserts.publication_id NOT IN (124)

when it is equivalent to:
WHERE adinserts.publication_id = 120

If it's equal to 120, it's also not equal to 124.

>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.

I think you need to LEFT JOIN adinserts against itself. The first one
has a record that matches publication_id = 120, and the second one has
NO record that matches publication_id = 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

No, that's not the way IN and NOT IN work.

>Is it acceptable to do WHERE ... IN () AND ... NOT IN ()?

It seems somewhat pointless to use IN (single_number) and NOT IN
(single_number) when = and != would be clearer.

>Or do I need to re-write my query to use sub-queries?

Sub-queries are not necessary, but re-write your query.
adinserts.publication_id and adinserts.publication_id refer to the
SAME record in adinserts, not two separate records. For what you
want, something like:

.... LEFT JOIN adinserts ad1 ON ad1.publication_id = publication.publication_id and ad1.publication_id = 120
LEFT JOIN adinserts ad2 ON ad2.publication_id = publication.publication_id and ad2.publication_id = 124
.... WHERE ad2.publication_id is null


>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