Posted by Plamen Ratchev on 02/08/07 14:24

Here are two ways to accomplish what you need in SQL Server 2000:

SELECT A.fldYear, A.fldCode1, A.fldCode2
FROM (SELECT fldYear, fldCode1, fldCode2
FROM (SELECT DISTINCT fldYear FROM MyTable) AS Y
CROSS JOIN (SELECT DISTINCT fldCode1, fldCode2 FROM
MyTable) AS C) AS A
LEFT OUTER JOIN MyTable M
ON M.fldYear = A.fldYear AND M.fldCode1 = A.fldCode1 AND M.fldCode2
= A.fldCode2
WHERE M.fldYear IS NULL

SELECT fldYear, fldCode1, fldCode2
FROM (SELECT 'A' AS flag, fldYear, fldCode1, fldCode2
FROM (SELECT DISTINCT fldYear FROM MyTable) AS Y
CROSS JOIN (SELECT DISTINCT fldCode1, fldCode2 FROM
MyTable) AS C
UNION ALL
SELECT 'M', fldYear, fldCode1, fldCode2 FROM MyTable) AS UA
GROUP BY fldYear, fldCode1, fldCode2
HAVING COUNT(*) = 1 AND MAX(flag) = 'A'

On SQL Server 2005 this is much easier:

SELECT fldYear, fldCode1, fldCode2
FROM (SELECT DISTINCT fldYear FROM MyTable) AS Y
CROSS JOIN (SELECT DISTINCT fldCode1, fldCode2 FROM MyTable) AS C
EXCEPT
SELECT fldYear, fldCode1, fldCode2 FROM MyTable

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]