SELECT Query Joining 2 Fields & Other Criteria
-
I am not sure how exactly to figure this one out: I have 2 tables, tblInvoices and tblSuppliers, that the way to make the join between the two are two fields (not keys), i.e. Vendor Number and Vendor Site Code. So I believe I got the first part correct, i.e. joining them:
SELECT t1.InvoiceID, t1.VendorNumber, t1.VendorSiteCode,
FROM tblInvoices as t1
INNER JOIN tblSuppliers as t2
ON t1.VendorNumber = t2.[Vendor Number]
AND t1.VendorSiteCode = t2.[Vendor Site Code]
But then I have some criteria to implement:
(1)Supplier has to be ACTIVE, i.e. Inactive Date is NULL from table t2, and
(2)where Supplier has had NO ACTIVITY since a date (8/1/03), i.e. NO INVOICES from table t1
Thus, it has led me to the following:
SELECT t1.InvoiceID, t1.VendorNumber, t1.VendorSiteCode,
FROM tblInvoices as t1
INNER JOIN tblSuppliers as t2
ON t1.VendorNumber = t2.[Vendor Number]
AND t1.VendorSiteCode = t2.[Vendor Site Code]
WHERE t2.[Supplier Inactive Date] IS NULL AND...
I am thinking of something like this for the last part after the AND... (pseudocode)
t2.[Vendor Number] AND [Vendor Site Code] NOT IN (SELECT VendorNumber, VendorSiteCode FROM tblInvoices WHERE CREATIONDT > '8/1/2003')
Can anyone give me some tips?
-
Try this:
SELECT t1.InvoiceID, t1.VendorNumber, t1.VendorSiteCode,
FROM tblInvoices as t1
INNER JOIN tblSuppliers as t2
ON t1.VendorNumber = t2.[Vendor Number]
AND t1.VendorSiteCode = t2.[Vendor Site Code]
WHERE t2.[Supplier Inactive Date] IS NULL AND
NOT EXISTS
(select null from tblInvoices tInv where t2.[vendor number] = tInv.VendorNumber and t2.[Vendor Site Code] = tInv.VendorSiteCode and creationdt > '8/1/03')
David W. Clary
MCSD
Data Miner 49er
Sr. Database Administrator, Ceiva Logic -
Thanks dclary for your help! I will try your suggestion in a minute.
FYI, I have added the following to the end of my code and it seems to be working. Though I would greatly appreciate anyone's critique of my design for I feel there is a more efficient way:
AND t2.[Vendor Number] NOT IN
(SELECT VendorNumber
FROM tblInvoices
WHERE CreationDT > '8/1/2003')
AND t2.[Vendor Site Code] NOT IN
(SELECT VendorSiteCode
FROM tblInvoices
WHERE CreationDT > '8/1/2003')
I will keep you all posted to see what occurs.
-
dclary:
I just tried your solution and it returned thousands more records that mine! I verified some of them that were not in my result set and they were correct!
I am assuming your way is the correct way!
Thanks!
-
Eek! I was right?
Someone write this down!
(for the record, I prefer EXISTS or NOT EXISTS to IN or NOT IN when referring to a table simply because it seems to (on larger tables) offer better performance.)
David W. Clary
MCSD
Data Miner 49er
Sr. Database Administrator, Ceiva Logic -
Dave
Yep, a while ago we did some tests on a not-so-large table using EXISTS (and NOT EXISTS) against IN (and NOT IN).
EXISTS was way faster.... even with a table of only a few hundred rows you could measure the difference in performance using a wristwatch (but make sure you've got an index on the "referencing" columns)
Sam
-
Holy cow! Right twice in the same thread!
/breaks out the celebratory Sobe.
David W. Clary
MCSD
Data Miner 49er
Sr. Database Administrator, Ceiva Logic
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply