Alternative for ANSI-SQL INTERSECT in Jet

[RookPSU]

Below are two queries which if combined with a UNION give me all customers who are overdue OR have purchased Floor Plans.
I need to modify this query to pull all customers which are Overdue AND have purchased floor plans. The ANSI-SQL Intersect command would work perfectly however I am unsure how to modify this using T-SQL syntax to achieve the desired result. Any takers?


SELECT CustomerRef_ListID FROM Invoice INNER JOIN StandardTerms ON Invoice.TermsRef_ListID = StandardTerms.ListID WHERE Invoice.DueDate < DateDiff('d',StandardTerms.StdDueDays,Now())
UNION SELECT CustomerRef_ListID FROM Invoice WHERE Invoice.TermsRef_ListID is Null AND Invoice.DueDate < Now()


UNION


SELECT CustomerRef_ListID FROM SalesReceipt INNER JOIN SalesReceiptLineDetail ON SalesReceipt.TxnID = SalesReceiptLineDetail.IDKEY WHERE ItemRef_FullName = 'Floor Plans'
UNION SELECT DISTINCT CustomerRef_ListID FROM Invoice INNER JOIN InvoiceLineDetail ON Invoice.TxnID = InvoiceLineDetail.IDKEY WHERE ItemRef_FullName = 'Floor Plans';

 

[lameid]

SELECT CustomerRef_ListID FROM Invoice INNER JOIN StandardTerms ON Invoice.TermsRef_ListID = StandardTerms.ListID WHERE Invoice.DueDate < DateDiff('d',StandardTerms.StdDueDays,Now())

AND CustomerRef_ListID IN(

SELECT CustomerRef_ListID FROM Invoice WHERE Invoice.TermsRef_ListID is Null AND Invoice.DueDate < Now()
)

AND CustomerRef_ListID IN(
SELECT CustomerRef_ListID FROM SalesReceipt INNER JOIN SalesReceiptLineDetail ON SalesReceipt.TxnID = SalesReceiptLineDetail.IDKEY WHERE ItemRef_FullName = 'Floor Plans'
)

AND CustomerRef_ListID IN(
SELECT DISTINCT CustomerRef_ListID FROM Invoice INNER JOIN InvoiceLineDetail ON Invoice.TxnID = InvoiceLineDetail.IDKEY WHERE ItemRef_FullName = 'Floor Plans'
)


Or you could just save each statement as a query and inner join them all in a new quey.

 

[lameid]

Oops... It helps if I remember the 2 queries part...

SELECT CustomerRef_ListID FROM Invoice INNER JOIN StandardTerms ON Invoice.TermsRef_ListID = StandardTerms.ListID WHERE Invoice.DueDate < DateDiff('d',StandardTerms.StdDueDays,Now())
UNION SELECT CustomerRef_ListID FROM Invoice WHERE Invoice.TermsRef_ListID is Null AND Invoice.DueDate < Now()

AND CustomerRef_ListID IN (

SELECT CustomerRef_ListID FROM SalesReceipt INNER JOIN SalesReceiptLineDetail ON SalesReceipt.TxnID = SalesReceiptLineDetail.IDKEY WHERE ItemRef_FullName = 'Floor Plans'
UNION SELECT DISTINCT CustomerRef_ListID FROM Invoice INNER JOIN InvoiceLineDetail ON Invoice.TxnID = InvoiceLineDetail.IDKEY WHERE ItemRef_FullName = 'Floor Plans'
);

That should be much better.

 

[PHV]

You wanted something like this ?
SELECT CustomerRef_ListID
FROM (
SELECT CustomerRef_ListID FROM Invoice INNER JOIN StandardTerms ON Invoice.TermsRef_ListID = StandardTerms.ListID WHERE Invoice.DueDate < DateDiff('d',StandardTerms.StdDueDays,Now())
UNION SELECT CustomerRef_ListID FROM Invoice WHERE Invoice.TermsRef_ListID is Null AND Invoice.DueDate < Now()
) A
WHERE CustomerRef_ListID IN (SELECT CustomerRef_ListID FROM (
SELECT CustomerRef_ListID FROM SalesReceipt INNER JOIN SalesReceiptLineDetail ON SalesReceipt.TxnID = SalesReceiptLineDetail.IDKEY WHERE ItemRef_FullName = 'Floor Plans'
UNION SELECT CustomerRef_ListID FROM Invoice INNER JOIN InvoiceLineDetail ON Invoice.TxnID = InvoiceLineDetail.IDKEY WHERE ItemRef_FullName = 'Floor Plans'
) B)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886