Nested query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Hilmar (anonymous_at_discussions.microsoft.com)
Date: 05/24/04 

Date: Mon, 24 May 2004 08:51:14 -0700

Hello all, I have two tables 'Names' and 'Date'. If you use the following statement

SELECT DISTINCT dbo.Names.CustName, dbo.Date.ccValidTo
FROM dbo.Date INNER JOIN
                      dbo.Name ON dbo.Date.CustID = dbo.Name.CustId

The records returned would be
Name ccValidTo
John 21.05.2003
John 21.05.2003
Mark 19.05.2003
Dave 20.04.2003
Dave 20.05.2003

Now for the problem, I need to create SQl query where I would only get the name that has not a valid date. In this example the query would only return Mark.

Something like:
SELECT name.Custname , Date.ccValidTo ONLY if Date.ccValidTo is less than today and there is no record with same name.CustID and Date.ccValidTo grater than today

I hope this is understandable.

Thanks
Hilmar Thor

Relevant Pages