471,108 Members | 1,316 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

NOT problem. this should be easy

Should be easy, but I've been trying to figure this out for about half
an hour with no luck. There is a table 'Cybex' which has all the Cybex
products we sell, and a table 'Datasheets' which contains a list of
the datasheets available for products. Both tables have a PartNumber
column.

On with the problem. If i do SELECT * FROM Cybex, it lists the 1000 or
so products. and then SELECT * FROM DataSheets, it lists the 800
datasheets we have for the cybex products. this should mean that there
are around 200 products without the datasheets

So, to get those 200 products without datasheets, shouldnt this work?
SELECT * FROM Cybex WHERE NOT(Cybex.PartNumber IN (SELECT
DataSheets.Partnumber FROM DataSheets))

It is returning 0 results. Any help would be appreciated, thanks.
Jul 20 '05 #1
3 2090
Andy,

DDL would help here. I'm assuming that PartNumber is a key for Cybex and
NOT NULL. But is Partnumber NULL for any datasheets? If any datasheet is
without a part number, you will get no rows in your result as NOT UNKNOWN =
UNKNOWN.

Try

SELECT *
FROM Cybex
WHERE PartNumber NOT IN (
SELECT PartNumber
FROM DataSheets
WHERE PartNumber IS NOT NULL);

or

SELECT *
FROM Cybex
WHERE NOT EXISTS (
SELECT *
FROM DataSheets
WHERE PartNumber = Cybex.PartNumber);

If my assumptions are wrong, try posting the DDL for your tables and we can
have another go.

Hope that helps,
Rich
"Andy Visniewski" <an**@pepperell.net> wrote in message
news:f6**************************@posting.google.c om...
Should be easy, but I've been trying to figure this out for about half
an hour with no luck. There is a table 'Cybex' which has all the Cybex
products we sell, and a table 'Datasheets' which contains a list of
the datasheets available for products. Both tables have a PartNumber
column.

On with the problem. If i do SELECT * FROM Cybex, it lists the 1000 or
so products. and then SELECT * FROM DataSheets, it lists the 800
datasheets we have for the cybex products. this should mean that there
are around 200 products without the datasheets

So, to get those 200 products without datasheets, shouldnt this work?
SELECT * FROM Cybex WHERE NOT(Cybex.PartNumber IN (SELECT
DataSheets.Partnumber FROM DataSheets))

It is returning 0 results. Any help would be appreciated, thanks.

Jul 20 '05 #2
an**@pepperell.net (Andy Visniewski) wrote in message news:<f6**************************@posting.google. com>...
Should be easy, but I've been trying to figure this out for about half
an hour with no luck. There is a table 'Cybex' which has all the Cybex
products we sell, and a table 'Datasheets' which contains a list of
the datasheets available for products. Both tables have a PartNumber
column.

On with the problem. If i do SELECT * FROM Cybex, it lists the 1000 or
so products. and then SELECT * FROM DataSheets, it lists the 800
datasheets we have for the cybex products. this should mean that there
are around 200 products without the datasheets

So, to get those 200 products without datasheets, shouldnt this work?
SELECT * FROM Cybex WHERE NOT(Cybex.PartNumber IN (SELECT
DataSheets.Partnumber FROM DataSheets))

It is returning 0 results. Any help would be appreciated, thanks.


Andy

A more elegent and better performing solution would be to do a left
join and select records where datasheet table returns null, i.e.

select * from cybex a left join datasheets b on a.partnumber =
b.partnumber
where b.partnumber is null

hth
Hodge
Jul 20 '05 #3
Tony,

Elegance is a subjective matter, but there's no reason that one of these
formulations should outperform the others in theory. As they describe the
same result, an intelligent DBMS should resolve them all to the same
(hopefully optimal) query plan. Consider this example using the pubs
database.

SELECT *
FROM publishers
WHERE pub_id NOT IN (
SELECT pub_id
FROM titles
WHERE pub_id IS NOT NULL);

SELECT *
FROM publishers AS p
WHERE NOT EXISTS (
SELECT *
FROM titles
WHERE pub_id=p.pub_id);

SELECT p.*
FROM
publishers AS p LEFT JOIN
titles AS t ON p.pub_id=t.pub_id
WHERE
t.pub_id IS NULL;
On my laptop, Query Analyzer shows me three nearly identical query plans.
As it happens, the third is the worst and the second is the best. (For the
sake of completeness, other formulations using ALL, ANY, and SOME are
handled exactly as NOT IN.) There's no reason that NOT EXISTS should always
produce the best plan or that the LEFT JOIN query should always produce the
worst (given that the plans are different in the first place). These things
are influenced by many factors. So far as I can see, the third technique
has only one constant advantage over the others: that it can be used easily
by non-programmers with drag-and-drop interfaces such as Access.

Rich
"Tony Hodgson" <to**@exactconsulting.co.uk> wrote in message
news:a7*************************@posting.google.co m...
an**@pepperell.net (Andy Visniewski) wrote in message

news:<f6**************************@posting.google. com>...
Should be easy, but I've been trying to figure this out for about half
an hour with no luck. There is a table 'Cybex' which has all the Cybex
products we sell, and a table 'Datasheets' which contains a list of
the datasheets available for products. Both tables have a PartNumber
column.

On with the problem. If i do SELECT * FROM Cybex, it lists the 1000 or
so products. and then SELECT * FROM DataSheets, it lists the 800
datasheets we have for the cybex products. this should mean that there
are around 200 products without the datasheets

So, to get those 200 products without datasheets, shouldnt this work?
SELECT * FROM Cybex WHERE NOT(Cybex.PartNumber IN (SELECT
DataSheets.Partnumber FROM DataSheets))

It is returning 0 results. Any help would be appreciated, thanks.


Andy

A more elegent and better performing solution would be to do a left
join and select records where datasheet table returns null, i.e.

select * from cybex a left join datasheets b on a.partnumber =
b.partnumber
where b.partnumber is null

hth
Hodge

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by geoffblanduk_nospam | last post: by
6 posts views Thread by TPJ | last post: by
39 posts views Thread by Martin Jørgensen | last post: by
34 posts views Thread by Simon Wigzell | last post: by
60 posts views Thread by =?ISO-8859-1?Q?Tom=E1s_=D3_h=C9ilidhe?= | last post: by
25 posts views Thread by biplab | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.