Alternate to Not In

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
fruffingAsked:
Who is Participating?
 
CluskittConnect With a Mentor Commented:
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
 
CluskittCommented:
Replace IN with EXISTS. It's more accurate anyway. Other than some NULL and empty differences, both do the same.
0
 
fruffingAuthor Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
CluskittCommented:
Yes, that's how it should be. Unless you're using older versions of access.
0
 
fruffingAuthor Commented:
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
 
lluddenCommented:
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
 
fruffingAuthor Commented:
lludden,

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

Thanks

 Doc5.doc
0
 
lluddenConnect With a Mentor Commented:
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
 
fruffingAuthor Commented:
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
 
CluskittCommented:
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
 
CluskittCommented:
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
 
fruffingAuthor Commented:
Cluskitt and lludden,

Here is the database where I am testing the query.

thanks for all your help.

ExEx.mdb
0
 
CluskittCommented:
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
 
CluskittCommented:
I am assuming that you're trying this with VB6/VBA. If you're trying a manual connection, use DDE instead.
0
 
fruffingAuthor Commented:
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
 
CluskittCommented:
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
 
fruffingAuthor Commented:
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
 
CluskittCommented:
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
 
fruffingAuthor Commented:
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
 
AkenathonCommented:
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
 
fruffingAuthor Commented:
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
 
fruffingAuthor Commented:
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
 
fruffingAuthor Commented:
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
 
fruffingAuthor Commented:
Resolved. I will explain and assign points on Monday.

thanks
0
 
fruffingAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.