SELECT Query Joining 2 Fields & Other Criteria

Question

Post reply

SELECT Query Joining 2 Fields & Other Criteria

eb24

SSC Eights!

Points: 823
More actions
February 1, 2005 at 9:22 am

#165889

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?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

dclary Old Hand Points: 321 More actions · Answer 1

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

eb24 SSC Eights! Points: 823 More actions · Answer 2

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.

eb24 SSC Eights! Points: 823 More actions · Answer 3

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!

dclary Old Hand Points: 321 More actions · Answer 4

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

Sam Shiell Ten Centuries Points: 1373 More actions · Answer 5

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

dclary Old Hand Points: 321 More actions · Answer 6

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