>I have two tables - we'll call them Customer and Bill. A customer can have zero or many bills, and it is linked by the CustomerID. What I want to do is pull a customer by CustomerID and also pull their last bill only if it is within 30 days old. If there isn't a bill that fits that criteria, I want null value.
>
>Can I do this with a JOIN? Anybody have any advice on how to do the filtering in the JOIN statement?
... or this one
SELECT *
FROM Customer c
LEFT JOIN Bill b ON b.CustomerID = c.CustomerID
AND Billdate = (SELECT MAX(BillDate) FROM Bill b2
WHERE b2.CustomerID = b.CustomerID
AND BillDate >= DATEADD(dd, -30, getdate) )
WHERE CustomerID = @CustomerID
--sb--