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

On 25 Sep, 09:56, Ben Bradley <no...@nowhere.net> wrote:
> 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

There is no point using IN when you only have one value, just use =
and !=.

However, I think that what you actually need to do is to make the
criteria part of the JOIN conditions and then test for NULL in the
WHERE clause.

Can you post some sample data and the expected results from that
sample data. Preferably the sample data will be in the form of a
phpmyadmin export, complete with the CREATE TABLE statement.

>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