Posted by teddysnips on 02/08/07 15:35

On Feb 8, 2:24 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> 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 Ratchevhttp://www.SQLStudio.com

Phenomenal! Thanks

Edward

• Next in forum: odd stored procedure behavior
• Prev in forum: Re: Locate rows in table where column value missing (I think!)
• Thread view: Re: Locate rows in table where column value missing (I think!)

[Reply to this message]