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.
Here's my query so far, and I'm sure it can be massively improved:
Code: Select all
SELECT DISTINCT `adbookings`.`booking_id`, `adbookings`.*
FROM `adbookings`
LEFT JOIN `adinserts` USING(`booking_id`)
LEFT JOIN `publications` ON `adinserts`.`publication_id`=`publications`.`publication_id`
WHERE `adinserts`.`publication_id` IN (120) AND `adinserts`.`publication_id` NOT IN (124)
ORDER BY `adbookings`.`backup_company_name`
LIMIT 0, 1000So I want to find the bookings that do appear in publication_id 120, but that don't appear in publication_id 124.
The query works fine and returns a correct result set when I just have WHERE `adinserts`.`publication_id` IN (120)
But when I add the AND ... NOT IN () it returns exactly the same result set.
Is it acceptable to do WHERE ... IN () AND ... NOT IN ()?
Or do I need to re-write my query to use sub-queries?
Thanks
Ben