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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

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

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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?
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Take a look at Useful Posts as I mentioned.
Post Reply