Locate rows in table where column value missing (I think!)

[teddysnips@hotmail.com]

SQL 2k, DDL below.

I have a simple table with the following data:

fldYear fldCode1 fldCode2
2000 ABC1 ABC12
2000 ABC1 ABC13
2001 ABC1 ABC12
2002 ABC1 ABC12
2002 ABC1 ABC13

I need to know, for every distinct combination of fldCode1 and
fldCode2, if there are any years missing.

For example,

SELECT DISTINCT fldCode1, fldCode2 FROM MyTable

returns
ABC1 ABC12
ABC1 ABC13

I need to know that in 2001 there was no entry for ABC1/ABC13

Thanks!

Edward

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[MyTable]') and OBJECTPROPERTY( id, N'IsUserTable') = 1)
drop table [dbo].[MyTable]
GO

CREATE TABLE [dbo].[MyTable] (
[fldYear] [char] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[fldCode1] [char] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[fldCode2] [char] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
) ON [PRIMARY]
GO

[Plamen Ratchev]

Re: Locate rows in table where column value missing (I think!)

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

[teddysnips@hotmail.com]

Re: Locate rows in table where column value missing (I think!)

On Feb 8, 2:24 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

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.c om

Phenomenal! Thanks

Edward