Select everything NOT sold in last 3 years

I have to do a select staement to show all partnumbers not sold in the last 3 years. I can easliy grab all part numbers that did sell in the last 3 years:

SELECT     dbo.PartNumbers.ManufacturerPartNumber
FROM         dbo.PartNumbers INNER JOIN
                      dbo.OrderDetails ON dbo.PartNumbers.PartNumberID = dbo.OrderDetails.PartNumberID
WHERE     (dbo.OrderDetails.ShipDate >= CONVERT(DATETIME, '2004-10-12 00:00:00', 102))

Now how do I get the opposite of that?
kevlauseAsked:
Who is Participating?
 
Sham HaqueConnect With a Mentor Senior SAP CRM ConsultantCommented:
SELECT     dbo.PartNumbers.ManufacturerPartNumber
FROM         dbo.PartNumbers
WHERE NOT EXISTS
(SELECT 1
FROM   dbo.OrderDetails
WHERE dbo.PartNumbers.PartNumberID = dbo.OrderDetails.PartNumberID
AND(dbo.OrderDetails.ShipDate >= CONVERT(DATETIME, '2004-10-12 00:00:00', 102)))
0
 
MikeTooleCommented:
Make it a left join to select all Partnumbers, put the date criteria in the Join and look for nulls in OrderDetails

SELECT     dbo.PartNumbers.ManufacturerPartNumber
FROM         dbo.PartNumbers Left JOIN
                      dbo.OrderDetails ON dbo.PartNumbers.PartNumberID = dbo.OrderDetails.PartNumberID
                  AND (dbo.OrderDetails.ShipDate >= CONVERT(DATETIME, '2004-10-12 00:00:00', 102))
Where  dbo.OrderDetails.PartNumberID
0
 
MikeTooleCommented:
Oops, missed the last bit:

Make it a left join to select all Partnumbers, put the date criteria in the Join and look for nulls in OrderDetails

SELECT     dbo.PartNumbers.ManufacturerPartNumber
FROM         dbo.PartNumbers Left JOIN
                      dbo.OrderDetails ON dbo.PartNumbers.PartNumberID = dbo.OrderDetails.PartNumberID
                  AND (dbo.OrderDetails.ShipDate >= CONVERT(DATETIME, '2004-10-12 00:00:00', 102))
Where  dbo.OrderDetails.PartNumberID IS Null
0
 
Nick UpsonPrincipal Operations EngineerCommented:
SELECT     dbo.PartNumbers.ManufacturerPartNumber
FROM         dbo.PartNumbers left JOIN
                      dbo.OrderDetails ON dbo.PartNumbers.PartNumberID = dbo.OrderDetails.PartNumberID
WHERE    dbo.OrderDetails.<some other field from this table> is NULL and
 (dbo.OrderDetails.ShipDate >= CONVERT(DATETIME, '2004-10-12 00:00:00', 102))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.