Solved

Alternate to Not In

Posted on 2011-03-18
25
256 Views
Last Modified: 2013-11-05
I need to select some records in one recordset that are not returned by another recordset where each recordset is the join of several tables. The below SQL works directly in MS Access as a query but will not work in ado because ado does not support the "IN" command.

Can someone help me with a LEFT JOIN or other technique that will do the same?

SELECT tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
FROM tCOHeader INNER JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
WHERE tCOHeader.CONumber NOT IN (SELECT DISTINCT tCOHeader.CONumber FROM (((tCOHeader LEFT JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber) LEFT JOIN tSLOTS on tCODetails.Slot = tSlots.Slot) LEFT JOIN tZoneList ON tSlots.ZoneID = tZoneList.ZoneID) WHERE tZoneList.ZoneID <> ‘ParameterName01’)
GROUP BY tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
0
Comment
Question by:fruffing
  • 13
  • 9
  • 2
  • +1
25 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35165633
Replace IN with EXISTS. It's more accurate anyway. Other than some NULL and empty differences, both do the same.
0
 

Author Comment

by:fruffing
ID: 35165711
Cluskitt,

I tried your suggestion. It did not work even in MS Access query. I got "Syntax error".

Note that the logic is NOT IN so I tried NOT Exists
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35165733
Yes, that's how it should be. Unless you're using older versions of access.
0
 

Author Comment

by:fruffing
ID: 35165827
I need a solution that works in adodb and neither "IN" or "EXISTS" will work there. I have seen several sample solutions using LEFT JOIN, but for very simple situations where there are no table joins. I can't seem to "adapt" those samples to this more complex need.
0
 
LVL 18

Accepted Solution

by:
Cluskitt earned 250 total points
ID: 35165886
IN and EXISTS are standard SQL and should work in access. Using adodb should make no difference. adodb is nothing more than a connection to the database. It's the database itself that will resolve the SQL query. If a query works on access, it has to work using adodb. If it doesn't, then your problem lies elsewhere.

As for the Syntax error you're getting with EXISTS, that is because EXISTS is a condition in itself, not as assignment/value checking. That is, it works like:

SELECT field1 FROM table t WHERE NOT EXISTS (SELECT 'a' FROM table t2 WHERE t2.field2='Some Condition' AND t2.ID=t1.ID)
0
 
LVL 18

Expert Comment

by:lludden
ID: 35165937
This should do it.
SELECT tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
FROM tCOHeader
      INNER JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
  LEFT OUTER JOIN
      (
            SELECT DISTINCT tCOHeader.CONumber
            FROM tCOHeader
                  LEFT JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
                  LEFT JOIN tSLOTS on tCODetails.Slot = tSlots.Slot
                  LEFT JOIN tZoneList ON tSlots.ZoneID = tZoneList.ZoneID)
            WHERE tZoneList.ZoneID <> ‘ParameterName01’
      ) C1 ON tCOHeader.CONumber = C1.CONumber
WHERE C1.CONumber IS NULL
GROUP BY tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate


0
 

Author Comment

by:fruffing
ID: 35166015
lludden,

I think you are on the right track; but I got a Syntax error. See attached document.

Thanks

 Doc5.doc
0
 
LVL 18

Assisted Solution

by:lludden
lludden earned 250 total points
ID: 35166616
There is an extra ) on the 3rd left join in that query.

SELECT tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
FROM tCOHeader
      INNER JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
  LEFT OUTER JOIN
      (
            SELECT DISTINCT tCOHeader.CONumber
            FROM tCOHeader
                  LEFT JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
                  LEFT JOIN tSLOTS on tCODetails.Slot = tSlots.Slot
                  LEFT JOIN tZoneList ON tSlots.ZoneID = tZoneList.ZoneID
            WHERE tZoneList.ZoneID <> ‘ParameterName01’
      ) C1 ON tCOHeader.CONumber = C1.CONumber
WHERE C1.CONumber IS NULL
GROUP BY tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
0
 

Author Comment

by:fruffing
ID: 35166764
lludden,

I saw that too, but removing it does not resolve the issue. I have tried to enclose other phrases in parenthesis with no luck. Note that my original SQL ran in MS Access, (but will not run in ado) so I know the table and fields names are accurate.  I need something that will run in ado, but I am trying your solution first in Access to make sure it works there....and so far no luck.

you return the 2nd recordset as alias C1; should the first record set also be return as an alias?

Could you take a close look at it again?

thanks

Frank
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35166795
Due to your problems so far, I have to say I'm more inclined to think that your problem lies in the adodb syntax, rather than SQL. As I said, if you have a query that worked in Access, it has to work when you use adodb to connect to the same database. If it doesn't, the problem lies in the connection string (or similar), not in the SQL query.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35166799
You should post your file(s) so we can test and see why it isn't working. If your file(s) contain data you don't wish others to see, replace it with sample dummy data of the same kind.
0
 

Author Comment

by:fruffing
ID: 35166902
Cluskitt and lludden,

Here is the database where I am testing the query.

thanks for all your help.

ExEx.mdb
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 18

Expert Comment

by:Cluskitt
ID: 35167129
Ok, from what I've been testing, qOneZone works well enough. Now, the problem you might be having with adodb is the parameter part. The query itself is sound.
I've had a similar problem when trying to merge access and word. VBA doesn't like parameter queries. What you should do is create a connection to the database, then dynamically change the SQL of the query, replacing the parameter with some value, for example, from a variable.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35167147
I am assuming that you're trying this with VB6/VBA. If you're trying a manual connection, use DDE instead.
0
 

Author Comment

by:fruffing
ID: 35167177
Cluskitt;

Been there; done that. That is where I started.

I should have included the query that lludden proposed. Copy that, plug it into Access and you will see it does not work (even after removing the extra parenthesis).

Frank

SELECT tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
FROM tCOHeader
      INNER JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
  LEFT OUTER JOIN
      (
            SELECT DISTINCT tCOHeader.CONumber
            FROM tCOHeader
                  LEFT JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
                  LEFT JOIN tSLOTS on tCODetails.Slot = tSlots.Slot
                  LEFT JOIN tZoneList ON tSlots.ZoneID = tZoneList.ZoneID
            WHERE tZoneList.ZoneID <> ‘ParameterName01’
      ) C1 ON tCOHeader.CONumber = C1.CONumber
WHERE C1.CONumber IS NULL
GROUP BY tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35167234
The thing is: there is no problem with using IN. If you change it to JOINS instead, you'll have the exact same problem, because you'll still have a parameter.

However, if you want the same thing but with JOINS, you'll have to try something like this:

SELECT tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
FROM tCOHeader
      INNER JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
  INNER JOIN
      (SELECT DISTINCT tCOHeader.CONumber
            FROM tCOHeader
                  LEFT JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber
                  LEFT JOIN tSLOTS ON tCODetails.Slot = tSlots.Slot
                  LEFT JOIN tZoneList ON tSlots.ZoneID = tZoneList.ZoneID
            WHERE tZoneList.ZoneID <> ‘ParameterName01’) C1
       ON tCOHeader.CONumber <> C1.CONumber
GROUP BY tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
0
 

Author Comment

by:fruffing
ID: 35167324
Cluskitt,

Did you test the query you provided into the database I uploaded? I just plugged it into MS Access here and it gives me a Syntax error (missing operator).

Frank
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35167480
Sorry, there was a slight problem with it. It would work fine on SQL server, but access is pickier. This was tested and works:

SELECT tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
FROM tCOHeader INNER JOIN (SELECT DISTINCT tCOHeader.CONumber FROM (((tCOHeader LEFT JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber) LEFT JOIN tSLOTS on tCODetails.Slot = tSlots.Slot) LEFT JOIN tZoneList ON tSlots.ZoneID = tZoneList.ZoneID) WHERE tZoneList.ZoneID <> ‘ParameterName01’) AS C1 ON tCOHeader.CONumber=C1.CONumber
GROUP BY tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate;
0
 

Author Comment

by:fruffing
ID: 35168004
Cluskitt,

That query works fine in Access (Thank you), but it (like the one with the NOT IN command) will not work in my use of ado in VB6. I DO replace the parameter with a string value. I use the same code to process other selection statements and they work fine so I think my connection etc. is right. I get an error that says "No vaue given for one or more required parameters". Does that trigger any suggestions for you?

Thanks again. I am pulling my hair out.

Frank
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 35168579
When parsing the string, anything NOT recognized as a table field will be considered a parameter. So if you have a field named say "ImportedDate" and by mistake you type "ImportDate", it will NOT match any field and will complain that you don't have a value for the PARAMETER "ImportDate". So doublecheck EVERY SINGLE field name in your SQL for differences. If the query works in Access, it ought to be fine, but only if you're connecting to the same DB, same user, etc. Doublecheck all those too :-)
0
 

Author Comment

by:fruffing
ID: 35168693
Akenathon,

To test this I am copying and pasting the whole selection string from the query in Access to a text field in a database table where the code picks it up and replaces the Parameter and then uses the modified string to (try to) open a recordset. So the field names are the same. When executed as an Access Query it prompts me for one parameter as it should. I replace that parameter before using it to open the recordset in the code.

Same database. Same User. Works for various other select statements. Everything looks like it should work. But it doesn't (yet)

Thanks for the suggestions.

Frank
0
 

Author Comment

by:fruffing
ID: 35168736
By the way the following is the exact sql select statement that works fine in Access and gives me exactly the results I need:

SELECT tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
FROM tCOHeader LEFT JOIN (SELECT DISTINCT tCOHeader.CONumber FROM (((tCOHeader LEFT JOIN tCODetails ON tCOHeader.CONumber = tCODetails.CONumber) LEFT JOIN tSLOTS on tCODetails.Slot = tSlots.Slot) LEFT JOIN tZoneList ON tSlots.ZoneID = tZoneList.ZoneID) WHERE tZoneList.ZoneID <> ‘ParameterName01’) AS C1 ON tCOHeader.CONumber=C1.CONumber WHERE C1.CONumber IS NULL
GROUP BY tCOHeader.CONumber, tCOHeader.BillingInfoCompanyName, tCOHeader.isOnPickList, tCOHeader.wasPicked, tCOHeader.someShort, tCOHeader.LastPickedDateTime, tCOHeader.ImportedDate
0
 

Author Comment

by:fruffing
ID: 35168824
Attached is the selection string just before I use it to open the recordset as follows:

ssSelectorParameters.Open sSql, gsConn, adOpenForwardOnly, adLockReadOnly, adCmdText

And the gsConn Connection string is:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & gsFishbone & ";Persist Security Info=False;JET OLEDB:Database Password=XXXXXXXXXXXXX"

Where gsFishone is the path to the database. SQL.txt
0
 

Author Comment

by:fruffing
ID: 35173001
Resolved. I will explain and assign points on Monday.

thanks
0
 

Author Closing Comment

by:fruffing
ID: 35180440
Cluskitt was awarded for his dogged insistence that the same SQL should work in Access and ado. LLudden was awarded for providing the solution based on LEFT JOINS that I asked for. The problem which we all missed was that I had used a "left quote" for the delimiting the text. It worked in Access but not in ado. Go figure.

Thanks. I love this service.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now