If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
'Not In' query not seem to be working
Hi
Access 2000 SP3. I have a Clients table which has 5400 records.When I run the below query to return all records in Clients table that have matching keys in Contacts table then I get 4047 records. SELECT Clients.ID, Clients.Company FROM Clients WHERE (((Clients.ID) In (SELECT Contacts.[Company ID] FROM Contacts WHERE (((Contacts.[Company Type])="C"))))); When I run the below query to return all records in Clients table that DO NOT have matching keys in Contacts table then I expect to get 1353 (5400-4047) records; SELECT Clients.ID, Clients.Company FROM Clients WHERE (((Clients.ID) Not In (SELECT Contacts.[Company ID] FROM Contacts WHERE (((Contacts.[Company Type])="C"))))); However I get 0 (nill) records. What is the problem and how can I make the query work to return all records in Clients table that do not have any matches in Contacts table? Thanks Regards |
#2
|
|||
|
|||
'Not In' query not seem to be working
On Sep 2, 1:05*am, "John" wrote:
Hi Access 2000 SP3. I have a Clients table which has 5400 records.When I run the below query to return all records in Clients table that have matching keys in Contacts table then I get 4047 records. SELECT Clients.ID, Clients.Company FROM Clients WHERE (((Clients.ID) In (SELECT Contacts.[Company ID] FROM Contacts WHERE (((Contacts.[Company Type])="C"))))); When I run the below query to return all records in Clients table that DO NOT have matching keys in Contacts table then I expect *to get 1353 (5400-4047) records; SELECT Clients.ID, Clients.Company FROM Clients WHERE (((Clients.ID) Not In (SELECT Contacts.[Company ID] FROM Contacts WHERE (((Contacts.[Company Type])="C"))))); However I get 0 (nill) records. What is the problem and how can I make the query work to return all records in Clients table that do not have any matches in Contacts table? Thanks Regards using NOT IN with a subselect is going to have hideous performance with large datasets. Use an outer join instead. Use the Find Unmatched query wizard, and the wizard will build the query for you. something like... SELECT Clients.ID, Clients.Company FROM Clients LEFT JOIN Contacts ON Clients.ClientID=Contacts.[Company ID] WHERE Contacts.[Company Type]="C" AND Contacts.[Company ID] IS NULL; |
#3
|
|||
|
|||
'Not In' query not seem to be working
Hi
Thanks. Did that and worked. I wasn't too worried about the performance as it is an adhoc query but its still puzzling why Not In version did not work. Thanks again. Regards wrote in message ... On Sep 2, 1:05 am, "John" wrote: Hi Access 2000 SP3. I have a Clients table which has 5400 records.When I run the below query to return all records in Clients table that have matching keys in Contacts table then I get 4047 records. SELECT Clients.ID, Clients.Company FROM Clients WHERE (((Clients.ID) In (SELECT Contacts.[Company ID] FROM Contacts WHERE (((Contacts.[Company Type])="C"))))); When I run the below query to return all records in Clients table that DO NOT have matching keys in Contacts table then I expect to get 1353 (5400-4047) records; SELECT Clients.ID, Clients.Company FROM Clients WHERE (((Clients.ID) Not In (SELECT Contacts.[Company ID] FROM Contacts WHERE (((Contacts.[Company Type])="C"))))); However I get 0 (nill) records. What is the problem and how can I make the query work to return all records in Clients table that do not have any matches in Contacts table? Thanks Regards using NOT IN with a subselect is going to have hideous performance with large datasets. Use an outer join instead. Use the Find Unmatched query wizard, and the wizard will build the query for you. something like... SELECT Clients.ID, Clients.Company FROM Clients LEFT JOIN Contacts ON Clients.ClientID=Contacts.[Company ID] WHERE Contacts.[Company Type]="C" AND Contacts.[Company ID] IS NULL; |
#4
|
|||
|
|||
'Not In' query not seem to be working
NOT IN( SELECT... ) where SELECT return a NULL among other records is a
condition that can never be TRUE, so no record are returned: x NOT IN (a, b, c) is the same as x a AND x b AND x c now, if a is null, that solves to NULL AND ... wich always solve to either NULL, with FALSE (never to TRUE). Note that JET removes the NULL if the list is constant. It is only in NOT IN(SELECT ... ) that you get the effect. Vanderghast, Access MVP "John" wrote in message ... Hi Thanks. Did that and worked. I wasn't too worried about the performance as it is an adhoc query but its still puzzling why Not In version did not work. Thanks again. Regards wrote in message ... On Sep 2, 1:05 am, "John" wrote: Hi Access 2000 SP3. I have a Clients table which has 5400 records.When I run the below query to return all records in Clients table that have matching keys in Contacts table then I get 4047 records. SELECT Clients.ID, Clients.Company FROM Clients WHERE (((Clients.ID) In (SELECT Contacts.[Company ID] FROM Contacts WHERE (((Contacts.[Company Type])="C"))))); When I run the below query to return all records in Clients table that DO NOT have matching keys in Contacts table then I expect to get 1353 (5400-4047) records; SELECT Clients.ID, Clients.Company FROM Clients WHERE (((Clients.ID) Not In (SELECT Contacts.[Company ID] FROM Contacts WHERE (((Contacts.[Company Type])="C"))))); However I get 0 (nill) records. What is the problem and how can I make the query work to return all records in Clients table that do not have any matches in Contacts table? Thanks Regards using NOT IN with a subselect is going to have hideous performance with large datasets. Use an outer join instead. Use the Find Unmatched query wizard, and the wizard will build the query for you. something like... SELECT Clients.ID, Clients.Company FROM Clients LEFT JOIN Contacts ON Clients.ClientID=Contacts.[Company ID] WHERE Contacts.[Company Type]="C" AND Contacts.[Company ID] IS NULL; |
#5
|
|||
|
|||
'Not In' query not seem to be working
I don't see that what MW says can apply in your case as it would mean that no
rows would be returned whether you use the IN or NOT IN predicates as the subquery is exactly the same in each case. Also what he says does not match my own experience. I can see no obvious explanation for the behaviour you are experiencing. I think you can probably exclude the [Company Type]="C" criterion from the subquery's WHERE clause, but the NOT IN predicate should have worked as you expect even so. Better than the NOT IN predicate, however, would be to use the NOT EXISTS predicate: SELECT * FROM Clients WHERE NOT EXISTS (SELECT * FROM Contacts WHERE Contacts.[Company ID] = Clients.ID); You could of course leave the [Company Type]="C" criterion in the above subquery and it should still work, but it looks to me like its unnecessary. As regards the solution by PL, I can only assume that you must have omitted the [Company Type] ="C" criterion for it to work, in which case it’s the best solution, though its usual to examine the primary key column of the table on the outer side of the join for IS NULL, not the foreign key. Otherwise, as posted with the inclusion of the [Company Type] ="C" criterion, it should return zero rows. This is because with that criterion included it restricts the query on a column in the table on the outer side of the left outer join. An outer join can only be restricted on a column in the table on the inner side. This makes sense, as if you think about the logic underlying an outer join, you can’t return rows from one table on the basis a criterion based on values in rows in another table which don't actually exist. In fact all it does is cause the query to behave as if the join is an INNER JOIN. If you have used the query exactly as posted by PL and it does return rows, then it would appear to be defying logic! I'd be very interested to know if this is the case, but unfortunately I'll be away incommunicado from now for a while, so my apologies in advance if you do respond to this post and don't hear back from me. Bearing in mind the inexplicable behaviour you are experiencing in use of the IN or NOT IN predicates and a subquery, however, nothing would surprise me. I do wonder whether there is something about the design of the tables which might explain the strange behaviour. I notice that you have a Company Type column in Contacts, but that would seem to me to be an attribute of Clients rather than Contacts, judging by the fact that the latter includes a Company column. Ken Sheridan Stafford, England "John" wrote: Hi Thanks. Did that and worked. I wasn't too worried about the performance as it is an adhoc query but its still puzzling why Not In version did not work. Thanks again. Regards wrote in message ... On Sep 2, 1:05 am, "John" wrote: Hi Access 2000 SP3. I have a Clients table which has 5400 records.When I run the below query to return all records in Clients table that have matching keys in Contacts table then I get 4047 records. SELECT Clients.ID, Clients.Company FROM Clients WHERE (((Clients.ID) In (SELECT Contacts.[Company ID] FROM Contacts WHERE (((Contacts.[Company Type])="C"))))); When I run the below query to return all records in Clients table that DO NOT have matching keys in Contacts table then I expect to get 1353 (5400-4047) records; SELECT Clients.ID, Clients.Company FROM Clients WHERE (((Clients.ID) Not In (SELECT Contacts.[Company ID] FROM Contacts WHERE (((Contacts.[Company Type])="C"))))); However I get 0 (nill) records. What is the problem and how can I make the query work to return all records in Clients table that do not have any matches in Contacts table? Thanks Regards using NOT IN with a subselect is going to have hideous performance with large datasets. Use an outer join instead. Use the Find Unmatched query wizard, and the wizard will build the query for you. something like... SELECT Clients.ID, Clients.Company FROM Clients LEFT JOIN Contacts ON Clients.ClientID=Contacts.[Company ID] WHERE Contacts.[Company Type]="C" AND Contacts.[Company ID] IS NULL; |
#6
|
|||
|
|||
'Not In' query not seem to be working
The OP does not ask for a better solution, just why the NOT IN does not work. Too bad you don't understand the explanation I supplied, since that is the case, and an ELEMENTARY case. Probably my fault, though, I mean, probably I was not clear enough. So IN (SELECT ... ) is totally different. Indeed if we consider x IN ( a , b, c) it is the same as x=a OR x=b OR x=c Dealing with OR, if a is null and x is equal to b, that returns TRUE: null OR true OR whatever returns TRUE. It is a different story with NOT IN since NOT IN implies AND conjunction, not OR, and so, NOT IN with one of the elements being a NULL NEVER returns TRUE.. FURTHERMORE, what is better than A SIMPLE TEST, in NORTHWIND: ---------------------------------- SELECT Employees.LastName FROM Employees WHERE (((Employees.LastName) Not In (SELECT iif(lastName="King", null, LastName) FROM employees))); ------------------------------------ which returns nothing and -------------------------------------- SELECT Employees.LastName FROM Employees WHERE (((Employees.LastName) In (SELECT iif(lastName="King", null, LastName) FROM employees))); -------------------------------------- which returns all records, except the one for King, Robert. A simple SOLUTION is to add a where clause in the subquery: SELECT ... FROM ... WHERE fieldName NOT IN( SELECT otherField ... WHERE NOT( otherFIeld IS NULL) ) Again, with Jet, that is applicable only with NOT IN( SELECT ... ) since with NOT IN( list of constant), Jet removes the NULL in the list, for that case. Vanderghast, Access MVP |
#7
|
|||
|
|||
'Not In' query not seem to be working
It is quite true that I had misread your original reply, and thought that you
were ascribing the behaviour both to the IN and NOT IN predicates. My fault, not yours. I see now that it was to the latter, and in that context the behaviour is of course as you say. If the OP is still with us I think it would perhaps contribute to their better understanding of the logical position to expand your explanation a little: x IN ( a , b, c) does, as you say, equate to: x=a OR x=b OR x=c but: x NOT IN ( a , b, c) firstly can be equated to: NOT (x IN (a, b, c)) and thence to: NOT(x=a OR x=b OR x=c) From this, applying DeMorgan's laws, we arrive at your: xa AND x=b AND x=c which, as you say, explains the behaviour. However, I do strongly disagree with the implication in your opening sentence that one should restrict one's answers to the precise question asked. If a better solution to a stated aim exists then I think it is incumbent on us draw this to the questioner's attention. Generally speaking the EXISTS predicate will give better performance than the IN predicate. This too has its problems with NULLs, but not in the context of the OP's question, I think. While this logical navel gazing is very interesting what seems to me to be of more importance from the OP's point of view is the implication of the fact that PL's solution, while apparently flawed, seems to have given the correct result. If used as posted it does tend to suggest that there is something not quite right in the logical model. Ken Sheridan Stafford, England "Michel Walsh" wrote: The OP does not ask for a better solution, just why the NOT IN does not work. Too bad you don't understand the explanation I supplied, since that is the case, and an ELEMENTARY case. Probably my fault, though, I mean, probably I was not clear enough. So IN (SELECT ... ) is totally different. Indeed if we consider x IN ( a , b, c) it is the same as x=a OR x=b OR x=c Dealing with OR, if a is null and x is equal to b, that returns TRUE: null OR true OR whatever returns TRUE. It is a different story with NOT IN since NOT IN implies AND conjunction, not OR, and so, NOT IN with one of the elements being a NULL NEVER returns TRUE.. FURTHERMORE, what is better than A SIMPLE TEST, in NORTHWIND: ---------------------------------- SELECT Employees.LastName FROM Employees WHERE (((Employees.LastName) Not In (SELECT iif(lastName="King", null, LastName) FROM employees))); ------------------------------------ which returns nothing and -------------------------------------- SELECT Employees.LastName FROM Employees WHERE (((Employees.LastName) In (SELECT iif(lastName="King", null, LastName) FROM employees))); -------------------------------------- which returns all records, except the one for King, Robert. A simple SOLUTION is to add a where clause in the subquery: SELECT ... FROM ... WHERE fieldName NOT IN( SELECT otherField ... WHERE NOT( otherFIeld IS NULL) ) Again, with Jet, that is applicable only with NOT IN( SELECT ... ) since with NOT IN( list of constant), Jet removes the NULL in the list, for that case. Vanderghast, Access MVP |
Thread Tools | |
Display Modes | |
|
|