help - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

help

Author

Topic

bamboo
Starting Member

5 Posts

Posted - 2007-11-06 : 15:32:52

what query will get TableB from TableA?. used count() but no lock. I use sql 2000.

TABLEA:
S    t     r      c     option
-    -     -      -     --	
S1   t1    r1     1     L
S1   t1    r2     1     kU
S1   t1    r3     1     L
S2   t2    r11    1     L
S2   t2    r12    1     L
S2   t2    r13    1     L
S3   t3    r21    2     kU
S3   t3    r22    2     L
S3   t3    r23    2     kU
continued likewise.

TABLEB:
S    t     c     option
-    -     -     --
S1   t1    1     kU
S2   t2    1     L
S3   t3    2     kU

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 15:39:57

I can think of at least 5 queries to get what you want.
You better post your business rules before we do some actual work here.

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-06 : 19:19:38

There is no defined logic for how you get your groupings. The is no way to determine if option should be "L" or "K" or any of the other aggregate functions.

This query will product your results...but....

[Code]
Select S1,t,c,option
From TABLEA
Where (S in ('S1','S3') and Option = 'kU') or (S = 'S2 and Option = 'L')
Group by S1,t,c,option
Order by S1,t,c
[/code]

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-11-06 : 22:14:01

this is much simpler, and works in any database:


select 'S1','t1',1,'kU' union all
select 'S2','t2',1,'L'  union all
select 'S3','t3',2,'kU'

elsasoft.org

bamboo
Starting Member

5 Posts

Posted - 2007-11-06 : 23:49:24

[code]TABLEA:
S t r c option
- - - - --
S1 t1 r1 1 L
S1 t1 r2 1 k
S1 t1 r3 1 L
S2 t2 r11 1 L
S2 t2 r12 1 L
S2 t2 r13 1 L
S3 t3 r21 2 k
S3 t3 r22 2 L
S3 t3 r23 2 k
continued likewise.

TABLEB:
S t c option
- - - --
S1 t1 1 k
S2 t2 1 L
S3 t3 2 k[/code]
TableA could become a big table. K can be in any row. if in TABLEA within a subgroup such as S1 there is at least one k option then in that subgroup, k becomes the dominant option. k can be randomly at any row.i do not still know how to achieve TABLEB easily but i keep trying. been away from SQL for the last 2.5 years.

my second question is why output from below procedure is:
[code]output:
1 t1 1 3
2 t2 1 0
instead of
1 t1 1 1
2 t2 1 0[/code]
what is wrong?.

[code]CREATE PROCEDURE MyLittleTest
As
create table #temp (s smallint, t nvarchar(30), r nvarchar(30),c smallint, ss int)
insert into #temp VALUES (1, 't1', 'r1', 1, 0);
insert into #temp VALUES (1, 't1', 'r2', 1, 1);
insert into #temp VALUES (1, 't1', 'r3', 1, 0);
insert into #temp VALUES (2, 't2', 'r11', 1, 0);
insert into #temp VALUES (2, 't2', 'r12', 1, 0);
insert into #temp VALUES (2, 't2', 'r13', 1, 0);

create table #temp1 (s1 smallint, t1 nvarchar(30), c1 smallint, ss1 int)
insert into #temp1
select T1.s, T1.t, T1.c,sum(T1.ss)
from #temp as T1, #temp as T2
where T1.s <> T2.s and T1.t <> T2.t
group by T1.s, T1.t, T1.c
order by T1.c asc
select *
from #temp1

DROP TABLE #temp1
DROP TABLE #temp
GO

output:
1 t1 1 3
2 t2 1 0[/code]

bamboo
Starting Member

5 Posts

Posted - 2007-11-07 : 09:55:58

what is wrong with the query?. thank you for any help.

output is:
1  t1  1  3
2  t2  1  0
instead of 
1  t1  1  1
2  t2  1  0

CREATE PROCEDURE MyLittleTest
As
create table #temp (s smallint, t nvarchar(30), r nvarchar(30),c smallint, ss int)
insert into #temp VALUES (1, 't1', 'r1', 1,  0);
insert into #temp VALUES (1, 't1', 'r2', 1,  1);
insert into #temp VALUES (1, 't1', 'r3', 1,  0);
insert into #temp VALUES (2, 't2', 'r11', 1, 0);
insert into #temp VALUES (2, 't2', 'r12', 1, 0);
insert into #temp VALUES (2, 't2', 'r13', 1, 0);

create table #temp1 (s1 smallint, t1 nvarchar(30), c1 smallint, ss1 int)
insert into #temp1
select T1.s, T1.t, T1.c,sum(T1.ss)
from #temp as T1, #temp as T2
where T1.s <> T2.s and T1.t <> T2.t
group by T1.s, T1.t, T1.c
order by T1.c asc
select *
from #temp1


DROP TABLE #temp1
DROP TABLE #temp
GO

output:
1  t1  1  3
2  t2  1  0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 10:05:51

quote:

Originally posted by bamboo

S    t     r      c     option
-    -     -      -     --	
S1   t1    r1     1     L
S1   t1    r2     1     k
S1   t1    r3     1     L
S2   t2    r11    1     L
S2   t2    r12    1     L
S2   t2    r13    1     L
S3   t3    r21    2     k
S3   t3    r22    2     L
S3   t3    r23    2     k

S    t     c     option
-    -     -     --
S1   t1    1     k
S2   t2    1     L
S3   t3    2     k

For group S2, there are no k options.
For group S3 there are 2 k options. What are the rules to handle these situations?

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-07 : 10:08:05

why yours doesn't work...

take out the 'sum' from your select and just look at the resultset you are inserting into #temp1 for a minute....? you can see that way that when you do a 'sum' it will add up '1', 3 times. that's why you're getting 3 in your last column.

that's why your query is wrong, but i'm not really clear what you're trying to acheive in the first place. can you explain any clearer?

Em

bamboo
Starting Member

5 Posts

Posted - 2007-11-07 : 11:18:59

yes. thank you for the hint. i knew that it was summing three times. what i try to do is to modify the query to make it bahave correctly to return:

1  t1  1  1
2  t2  1  0
instead of 
1  t1  1  3
2  t2  1  0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources