|
| |
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
 |
RE: Joining 2 databases - 5/17/2007 16:55:16
There must be something about your table structure that I am failing to understand. I have done this type of query several times in the past. For example, I have a table named employees and another named narratives. I have a query that tells me all employees who have no entry in the narratives table. This is what it looks like:
SELECT employees.ID FROM Employees WHERE employees.ID NOT IN (SELECT DISTINCT narratives.userID FROM narratives)
So, following that same logic, this basic query should work for your situation:
SELECT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking)
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
|
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
RE: Joining 2 databases - 5/18/2007 10:51:13
I totally agree, so I started going back to the basics.
After a little playing and guessing I came up with this. Maybe it will help us go in a different direction.
1 thing is, I changed the table name UPS_Invoices.TrackingID to UPS_Invoices.UPSTrackingID, I was getting a little confused.
So with that said I tried this:SELECT DISTINCT ups_invoices.UPSTrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ups_invoices ON AR_19InvoiceHistoryTracking.TrackingID = ups_invoices.UPSTrackingID WHERE ups_invoices.UPSTrackingID = '1ZXXXXXXX351733986'
It has 1 result, as it should.
I replaced = with <>, like '1Z%' and NOT LIKE
Everything returns results as expected.
Thoughts?
Thanks.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/18/2007 11:58:36
OK. So did you try this:
SELECT UPS_Invoices.UPSTrackingID FROM UPS_Invoices WHERE UPS_Invoices.UPSTrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking)
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
RE: Joining 2 databases - 5/18/2007 12:23:12
No results, DRAT ! It should work.
Funny thing here. This returns the exact opposite of what I need.fp_sQry="SELECT distinct ups_invoices.UPSTrackingID, UPS_Invoices.invoicedate, UPS_Invoices.accountnumber FROM AR_19InvoiceHistoryTracking INNER JOIN ups_invoices ON AR_19InvoiceHistoryTracking.TrackingID = ups_invoices.UPSTrackingID WHERE ups_invoices.invoicedate = '"& myDate &"' AND ups_Invoices.accountnumber='0000XXXXXX'
AND it works, but If I replace
AR_19InvoiceHistoryTracking.TrackingID = ups_invoices.UPSTrackingID
with
AR_19InvoiceHistoryTracking.TrackingID <> ups_invoices.UPSTrackingID
I get timeout issues.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/18/2007 13:01:31
I know I am just repeating myself now, but just let me verify this one more time. Does this return results:
SELECT UPS_Invoices.UPSTrackingID FROM UPS_Invoices
And does this return results?
SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking
If so, are all of the IDs in the second query also found in the first?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
RE: Joining 2 databases - 5/18/2007 14:16:43
Yes
Yes
Not "All" records
The 2nd query will have older records which will match to nothing.
But there a lot of records that do match. In fact by running it in Access with the querySELECT distinct dbo_AR_19InvoiceHistoryTracking.TrackingID, dbo_ups_invoices.UPSTrackingID
FROM dbo_AR_19InvoiceHistoryTracking INNER JOIN dbo_ups_invoices ON dbo_AR_19InvoiceHistoryTracking.TrackingID = dbo_ups_invoices.UPSTrackingID;
Returns 36088 records
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/18/2007 20:38:01
I have probably made some wrong assumptions about your setup. I thought UPS_Invoices probably contained all trackingIDs while AR_19InvoiceHistoryTracking contained trackingIDs from UPS_Invoices that had met some sort of criteria. Can you elaborate further on your setup?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
RE: Joining 2 databases - 5/22/2007 11:31:43
Sorry for not replying sooner, but I dug my heels in and came up with something that I "think", "hope", & "pray" is working.
SELECT DISTINCT UPS_Invoices.InvoiceDate, UPS_Invoices.[Transaction Date], UPS_Invoices.AccountNumber, UPS_Invoices.UPSTrackingID, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, UPS_Invoices.Ref4, UPS_Invoices.Ref5
FROM UPS_Invoices
LEFT JOIN AR_19InvoiceHistoryTracking ON UPS_Invoices.UPSTrackingID = AR_19InvoiceHistoryTracking.TrackingID
WHERE (((UPS_Invoices.InvoiceDate)='::Invoice_Date::') AND ((AR_19InvoiceHistoryTracking.TrackingID) Is Null));
I could not have done it without your help.
Thanks again.
If I run into issues I'll let you know.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
