By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

(Posted in error earlier to SQL Server group - at that time I was led
to believe that this was a SQL Server table)

Feb 8 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Hi Edward,

Try this:

Select fldYear From (Select Distinct fldYear, fldCode1, fldCode2 From
Table1) t1
Group By fldYear
Having Count(*) < (Select Count(*) As cnt From (Select Distinct
fldCode1, fldCode2 From Table1) t2)

You can replace Table1 with the name of your actual table
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Feb 8 '07 #2

P: n/a
On 8 Feb, 17:21, Rich P <rpng...@aol.comwrote:
Hi Edward,

Try this:

Select fldYear From (Select Distinct fldYear, fldCode1, fldCode2 From
Table1) t1
Group By fldYear
Having Count(*) < (Select Count(*) As cnt From (Select Distinct
fldCode1, fldCode2 From Table1) t2)

You can replace Table1 with the name of your actual table

Rich
Thanks, Rich, that certainly gives me the year. Unfortunately I need
to identify the actual row in the table - in other words, the values
for fldCode1 and fldCode2 as well. The table design isn't firmed up
yet so I could add a Primary Key column. Any thoughts?

Edward

Feb 9 '07 #3

P: n/a
Hello again,

try it this way - this statement will isolate the missing fldcode values
- note that I concatnated them for simplicity:

Select Distinct fldCode1 + fldcod2 As fldcod3 From Table1 tm Left Join
(Select fldcod1 + fldcod2 As fldCode3 From Table1 ta Inner Join
(Select fldYear From (Select Distinct fldYear, fldCode1, fldCode2 From
Table1) t1
Group By fldYear
Having Count(*) < (Select Count(*) As cnt From (Select Distinct
fldCode1, fldCode2 From Table1) t2)) tb On ta.fldYear = tb.fldYear)) tn
On t1.fldcod1 + tm.fldcod2 = tn.fldcode3
Where tnfldcod3 is null

So what I am doing here first is using the original query to isolate the
year that is missing the fldcodes, that would be 2001. Then I add the
fldcodes to the 2001 row which is DOES contain - that's the Inner Join
statement (joining the table to itself). This will return only the
fldcodes that 2001 contains (if any). Then I do a left join from the
original table to this inner join table to get the fldcodes that do not
exist in the inner join table
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Feb 9 '07 #4

P: n/a
On 9 Feb, 16:39, Rich P <rpng...@aol.comwrote:
Hello again,

try it this way - this statement will isolate the missing fldcode values
- note that I concatnated them for simplicity:

Select Distinct fldCode1 + fldcod2 As fldcod3 From Table1 tm Left Join
(Select fldcod1 + fldcod2 As fldCode3 From Table1 ta Inner Join
(Select fldYear From (Select Distinct fldYear, fldCode1, fldCode2 From
Table1) t1
Group By fldYear
Having Count(*) < (Select Count(*) As cnt From (Select Distinct
fldCode1, fldCode2 From Table1) t2)) tb On ta.fldYear = tb.fldYear)) tn
On t1.fldcod1 + tm.fldcod2 = tn.fldcode3
Where tnfldcod3 is null

So what I am doing here first is using the original query to isolate the
year that is missing the fldcodes, that would be 2001. Then I add the
fldcodes to the 2001 row which is DOES contain - that's the Inner Join
statement (joining the table to itself). This will return only the
fldcodes that 2001 contains (if any). Then I do a left join from the
original table to this inner join table to get the fldcodes that do not
exist in the inner join table

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Rich

Unfortunately I can't get this to work as I get a persistent "Syntax
error in FROM clause" which I can't get rid of. It doesn't help that
I don't really understand how you're doing what you're doing! I know
you explained it, but I just can't get my head round it!

Edward

Feb 13 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.