473,219 Members | 1,676 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,219 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 2174
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Chris Bolus | last post by:
I'm a teacher using MS Access on an RMConnect 2.4 network. On some workstations both I and my students sometimes get an error message when attempting to insert a command button on a form which...
3
by: geoffblanduk_nospam | last post by:
Given a set of intervals {i1, i2, i3, ...} a list is produced; the base items (0) are placed one at a time into the stream and after the ix one a interval item (1, 2, 3, etc) of the correct type is...
4
by: snowweb | last post by:
I am trying to implement a CSS hierarchical unfolding menu on a site. The thing is, it needs to be dynamically populated from the results of a database query. I previously had the menu working but...
6
by: TPJ | last post by:
Help me please, because I really don't get it. I think it's some stupid mistake I make, but I just can't find it. I have been thinking about it for three days so far and I still haven't found any...
39
by: Martin Jørgensen | last post by:
Hi, I'm relatively new with C-programming and even though I've read about pointers and arrays many times, it's a topic that is a little confusing to me - at least at this moment: ---- 1)...
34
by: Simon Wigzell | last post by:
document...focus() will scroll the form to move the specified text field into view on everything I have tried it with except Safari on the MAC. The form doesn't move. Any work around? Thanks.
60
by: =?ISO-8859-1?Q?Tom=E1s_=D3_h=C9ilidhe?= | last post by:
On May 3, 8:09 am, apati...@gmail.com wrote: A programmer that uses Vista? :O Vista is a hog of an operating system. Downgrade to Windows XP or get yourself a Linux distro.
0
by: gdrude | last post by:
I should start of by letting everyone know that I am not an experienced programmer. I have read a number of books on OOP languages and settled on Python to solve this particular problem. ...
25
by: biplab | last post by:
Hi all, I am using TC 3.0..there if I declare a integer array with dimension 162*219...an error msg saying that too long array is shown....what should I do to recover from this problem???
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.