NOT problem. this should be easy

[Andy Visniewski]

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.

[Rich Dillon]

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.

[Tony Hodgson]

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

[Rich Dillon]

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