Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Qry Help

Author  Topic 

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-01-14 : 16:39:10
I have a table with two columns. Each Alphabet in Col1 should associated with 4 values in Col2 ( 1.e 10, 20 , 30 and 40 ).
How to find out the missing rows for each Alphabet.

Ex Only two rows are there for B with values 40 and 20.I need to fin
out the missing Combination ( B with 10 and 30 ). I don't want to use Cursor for this.

Col1--Col2
A-----10
A-----20
A-----30
A-----40
B-----40
B-----20
C-----10
C-----20
C-----30
D-----10
D-----20
D-----30
D-----40

Thanks for the help
Venu

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-14 : 17:29:48
Here is one possibility:

create table #yourTable (col1 char(1), col2 int)
insert #yourTable
select 'A', 10 union all
select 'A', 20;

with AllVals as (
select a
,n
from (
select char(v.number) a
from master..spt_values v
where type = 'p'
and number between 65 and 90
) alph
cross join (select 10 n union all select 20 union all select 30 union all select 40) num
)
select AllVals.a, AllVals.n
from AllVals
left join #yourTable yt on yt.col1 = AllVals.a and yt.col2 = AllVals.n
where yt.col1 is null
and AllVals.a = 'A' --Just the "A"s for this example

drop table #yourTable

output:
a n
---- -----------
A 30
A 40


Be One with the Optimizer
TG
Go to Top of Page

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-01-14 : 18:08:58
I am runing SQL Server 2000

Thx
Venu
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-14 : 18:25:31
oh ok - just a minor change (the concepts definately don't require 2005 functionality):

create table #yourTable (col1 char(1), col2 int)
insert #yourTable
select 'A', 10 union all
select 'A', 20;


select AllVals.a, AllVals.n
from (
select a
,n
from (
select char(v.number) a
from master..spt_values v
where type = 'p'
and number between 65 and 90
) alph
cross join (select 10 n union all select 20 union all select 30 union all select 40) num
) allVals
left join #yourTable yt on yt.col1 = AllVals.a and yt.col2 = AllVals.n
where yt.col1 is null
and AllVals.a = 'A' --Just the "A"s for this example

drop table #yourTable

output:
a n
---- -----------
A 30
A 40


Be One with the Optimizer
TG
Go to Top of Page

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-01-14 : 18:47:57
Thanks TG. Works Perfect.
How will it be from the Performance Stand point if I have 30000 diff Symbols in Col1 and 7 types in Col 2.
Cross Join of 7 * 30000 = 210000

Thanks Again

Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-14 : 18:55:23
not sure. If those values are static, you could use this code to create an actual table with a primary key instead of a derived table. So if your other table is also indexed, 210000 rows shouldn't be any problem at all.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -