RE: Joining 2 databases

All Forums >> [Web Development] >> ASP and Database

Message

BeTheBall -> 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)

xgd -> 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 -> 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)

xgd -> 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 -> 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?

xgd -> 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 query SELECT 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 -> 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?

xgd -> 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.

BeTheBall -> RE: Joining 2 databases (5/22/2007 12:44:21)
Glad you found a solution. I'm also glad this thread will stop just shy of 100 posts. [;)]

Page: << < prev 1 2 3 [4]

0.0625