By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,691 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

Nested query bug?

P: n/a
Hi

I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword Not. Is this
a bug? I am using access 2000 sp3.

Thanks

Regards
Aug 18 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Obvious question first, are you sure there are records which match the
criteria? I've just tried your SQL and it works fine.

I suspect, though, that you are expecting this to show data for Clients
where the [Company 2] field is Null, this won't happen.

The SQL you have will show Clients where the data in the [Company 2] field
does not exist in the Company field

I suspect you need something like this ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE (((
Clients.[Company 2]) Not In (SELECT [Company] FROM [Clients])))
OR
(((Clients.[Company 2]) Is Null));

.... which will show Clients where the data in the [Company 2] field does not
exist in the Company field and also the Clients where there is no data in
the [Company 2] field.

or possibly just ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE
(((Clients.[Company 2]) Is Null));

.... which will show Clients where there is no data in the [Company 2] field.

--

Terry Kreft
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:tv******************************@pipex.net...
Hi

I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword Not. Is
this
a bug? I am using access 2000 sp3.

Thanks

Regards


Aug 18 '06 #2

P: n/a
It does not give any result i.e. no records while I know there should be
some. One example is below;

Company ID Company 2
Liffe 5483 Simon Pleydell

Simon Pleydell does not exist in the clients.company field any where in the
table and I have verified this by manually trying to find 'Simon Pleydell'
in the company field. Any ideas why it would not work? I am not looking for
nulls.

Thanks

Regards

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:EN******************************@eclipse.net. uk...
Obvious question first, are you sure there are records which match the
criteria? I've just tried your SQL and it works fine.

I suspect, though, that you are expecting this to show data for Clients
where the [Company 2] field is Null, this won't happen.

The SQL you have will show Clients where the data in the [Company 2] field
does not exist in the Company field

I suspect you need something like this ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE (((
Clients.[Company 2]) Not In (SELECT [Company] FROM [Clients])))
OR
(((Clients.[Company 2]) Is Null));

... which will show Clients where the data in the [Company 2] field does
not
exist in the Company field and also the Clients where there is no data in
the [Company 2] field.

or possibly just ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE
(((Clients.[Company 2]) Is Null));

... which will show Clients where there is no data in the [Company 2]
field.

--

Terry Kreft
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:tv******************************@pipex.net...
>Hi

I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword Not. Is
this
>a bug? I am using access 2000 sp3.

Thanks

Regards



Aug 18 '06 #3

P: n/a
Could you join the 2 tables on the names, change it to an outer join, and
then use Null as the criteria to select those that don't match?

Something like this:
SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients LEFT JOIN Clients AS Dupe
ON Clients.Company = Dupe.[Company 2]
WHERE Dupe.ClientID Is Null;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.infovis.co.ukwrote in message
news:Ct******************************@pipex.net...
It does not give any result i.e. no records while I know there should be
some. One example is below;

Company ID Company 2
Liffe 5483 Simon Pleydell

Simon Pleydell does not exist in the clients.company field any where in
the
table and I have verified this by manually trying to find 'Simon Pleydell'
in the company field. Any ideas why it would not work? I am not looking
for nulls.

Thanks

Regards

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:EN******************************@eclipse.net. uk...
>Obvious question first, are you sure there are records which match the
criteria? I've just tried your SQL and it works fine.

I suspect, though, that you are expecting this to show data for Clients
where the [Company 2] field is Null, this won't happen.

The SQL you have will show Clients where the data in the [Company 2]
field
does not exist in the Company field

I suspect you need something like this ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE (((
Clients.[Company 2]) Not In (SELECT [Company] FROM [Clients])))
OR
(((Clients.[Company 2]) Is Null));

... which will show Clients where the data in the [Company 2] field does
not
exist in the Company field and also the Clients where there is no data in
the [Company 2] field.

or possibly just ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE
(((Clients.[Company 2]) Is Null));

... which will show Clients where there is no data in the [Company 2]
field.

--

Terry Kreft
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:tv******************************@pipex.net.. .
>>Hi

I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword Not. Is
this
>>a bug? I am using access 2000 sp3.

Aug 18 '06 #4

P: n/a
Senior moment Allen?

I think you meant

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients LEFT JOIN Clients AS Dupe ON Clients.[Company 2] = Dupe.Company
WHERE (((Dupe.ID) Is Null));

--

Terry Kreft
"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:44*********************@per-qv1-newsreader-01.iinet.net.au...
Could you join the 2 tables on the names, change it to an outer join, and
then use Null as the criteria to select those that don't match?

Something like this:
SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients LEFT JOIN Clients AS Dupe
ON Clients.Company = Dupe.[Company 2]
WHERE Dupe.ClientID Is Null;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.infovis.co.ukwrote in message
news:Ct******************************@pipex.net...
It does not give any result i.e. no records while I know there should be
some. One example is below;

Company ID Company 2
Liffe 5483 Simon Pleydell

Simon Pleydell does not exist in the clients.company field any where in
the
table and I have verified this by manually trying to find 'Simon
Pleydell'
in the company field. Any ideas why it would not work? I am not looking
for nulls.

Thanks

Regards

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:EN******************************@eclipse.net. uk...
Obvious question first, are you sure there are records which match the
criteria? I've just tried your SQL and it works fine.

I suspect, though, that you are expecting this to show data for Clients
where the [Company 2] field is Null, this won't happen.

The SQL you have will show Clients where the data in the [Company 2]
field
does not exist in the Company field

I suspect you need something like this ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE (((
Clients.[Company 2]) Not In (SELECT [Company] FROM [Clients])))
OR
(((Clients.[Company 2]) Is Null));

... which will show Clients where the data in the [Company 2] field
does
not
exist in the Company field and also the Clients where there is no data
in
the [Company 2] field.

or possibly just ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE
(((Clients.[Company 2]) Is Null));

... which will show Clients where there is no data in the [Company 2]
field.

--

Terry Kreft
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:tv******************************@pipex.net...
Hi

I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword Not.
Is
this
a bug? I am using access 2000 sp3.


Aug 18 '06 #5

P: n/a

OK, that's strange, I actually tested the SQL against data and your original
SQl certainly should have worked the way you want it to. BTW I'muing Acess
2003.

Allan Browne has suggested using a Left Outer join, e.g.
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
LEFT JOIN
Clients AS Dupe
ON
Clients.[Company 2] = Dupe.Company
WHERE
(((Dupe.ID) Is Null));

the only problem with this is that it will bring through the companies where
the [Company 2] Field Is Null as well, which is a larger set than you want.

You could try ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
LEFT JOIN
Clients AS Dupe
ON
Clients.[Company 2] = Dupe.Company
WHERE
(((Dupe.ID) Is Null))
AND
Clients.[Company 2] Is Not Null;

.... which should work as you want.

--

Terry Kreft
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:Ct******************************@pipex.net...
It does not give any result i.e. no records while I know there should be
some. One example is below;

Company ID Company 2
Liffe 5483 Simon Pleydell

Simon Pleydell does not exist in the clients.company field any where in
the
table and I have verified this by manually trying to find 'Simon Pleydell'
in the company field. Any ideas why it would not work? I am not looking
for
nulls.

Thanks

Regards

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:EN******************************@eclipse.net. uk...
Obvious question first, are you sure there are records which match the
criteria? I've just tried your SQL and it works fine.

I suspect, though, that you are expecting this to show data for Clients
where the [Company 2] field is Null, this won't happen.

The SQL you have will show Clients where the data in the [Company 2]
field
does not exist in the Company field

I suspect you need something like this ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE (((
Clients.[Company 2]) Not In (SELECT [Company] FROM [Clients])))
OR
(((Clients.[Company 2]) Is Null));

... which will show Clients where the data in the [Company 2] field does
not
exist in the Company field and also the Clients where there is no data
in
the [Company 2] field.

or possibly just ...
SELECT
Clients.ID, Clients.Company, Clients.[Company 2]
FROM
Clients
WHERE
(((Clients.[Company 2]) Is Null));

... which will show Clients where there is no data in the [Company 2]
field.

--

Terry Kreft
"John" <Jo**@nospam.infovis.co.ukwrote in message
news:tv******************************@pipex.net...
Hi

I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword Not. Is
this
a bug? I am using access 2000 sp3.

Thanks

Regards



Aug 18 '06 #6

P: n/a
"John" <Jo**@nospam.infovis.co.ukwrote in
news:tv******************************@pipex.net:
I have the following query;

SELECT Clients.ID, Clients.Company, Clients.[Company 2]
FROM Clients
WHERE Clients.[Company 2] Not In (SELECT [Company] FROM [Clients])

When I do not use the keyword Not it works but not with keyword
Not. Is this a bug? I am using access 2000 sp3.
What's wrong with a join?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 18 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.