Page 1 of 1

MySQL query with WHERE ... IN () AND ... NOT IN ()

Posted: Mon Sep 24, 2007 6:03 am
by batfastad
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.

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, 1000
My problem is the WHERE ... IN () AND ... NOT IN ()
So 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

Posted: Mon Sep 24, 2007 6:55 am
by superdezign
You can replace your IN() and NOT IN() with == and !=, unless you plan to use multiple values.

And what's the problem? Does it still give you the results where publication_id is 124?

Posted: Mon Sep 24, 2007 7:20 am
by batfastad
Hi, thanks for your quick reply.

Yeah I am planning to have multiple values in the IN () and NOT IN ()
I'll have a maximum of 20 values in each side.

I bet there's a way more efficient to do it than IN and NOT IN, but this is an intranet application to be used once a week by a single user on a server that will only be running this and a few other scripts.

The actual problem I'm getting is this...
  • 1)

    Code: Select all

    WHERE `adinserts`.`publication_id` IN ( 120 )
    gives me 45 results... correct. There are 45 bookings that should appear in publication #120.

    2)

    Code: Select all

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

    3)

    Code: Select all

    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.
Any ideas?

Thanks
Ben

Posted: Mon Sep 24, 2007 9:30 am
by feyd
The last query is odd. Technically, it is the same as the first. See, publicationId can't have two values at the same time. That's what it is looking for.

What is the logic you are attempting to do? I'm going to guess you want to find bookings that are different between the publications? If so, take a look in Useful Posts for a linked thread about finding dissimilar values.

Posted: Mon Sep 24, 2007 10:05 am
by batfastad
Hi feyd. Thanks for replying!

Right ok.

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

What I'm trying to do is return all the adbookings that have adinserts records with publication_ids of say: 123,124,125
But not any adbookings that have adinserts records with publication_id of 126,127

I thought the way to do this would be something like this...

Code: Select all

WHERE `adinserts`.`publication_id`IN (123,124,125) AND `adinserts`.`publication_id` NOT IN (126,127)
Does that help in explaining what I'm trying to do?

Thanks, Ben

Posted: Mon Sep 24, 2007 10:11 am
by feyd
Take a look at Useful Posts as I mentioned.