help with query.... - 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 2005 Forums

Transact-SQL (2005)

help with query....

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2008-11-12 : 09:06:23

declare @tbl1 table(id int, idname varchar(6))
insert into @tbl1
select 1, 'd/r-01' union all
select 2, 'd/r-02' union all
select 3, 'd/r-03'

declare @tbl2 table(pk int, rowid int, id int)
insert into @tbl2
select 1, 1, 3 union all
select 2, 1, 2 union all
select 3, 1, 3 union all
select 4, 2, 2 union all
select 5, 1, 3 union all
select 6, 2, 2 union all
select 7, 2, 2 union all
select 8, 1, 3 union all
select 9, 4, 2 union all
select 10, 4, 1 union all
select 11, 4, 0 union all
select 12, 2, 2 union all
select 13, 2, 0

select a.pk, a.rowid, a.id, b.idname

from @tbl2 a
left join @tbl1 b
on a.id=b.id
order by rowid

pk rowid id idname
1 1 3 d/r-03
2 1 2 d/r-02
3 1 3 d/r-03
8 1 3 d/r-03
5 1 3 d/r-03
6 2 2 d/r-02
7 2 2 d/r-02
12 2 2 d/r-02
13 2 0 NULL
4 2 2 d/r-02
9 4 2 d/r-02
10 4 1 d/r-01
11 4 0 NULL

however I want this result:

pk rowid id idname
1 1 3 d-03
2 1 2 d-02
3 1 3 d-03
8 1 3 d-03
5 1 3 d-03
6 2 2 r-02
7 2 2 r-02
12 2 2 r-02
13 2 0 r-02
4 2 2 r-02
9 4 2 r-02
10 4 1 r-01
11 4 0 r-02

D assign only if id is not null from the group
R assign only if id is null from the group

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 10:05:57

[code]select a.pk, a.rowid, a.id,
CASE WHEN COALESCE(c.RecCnt,0)>0 THEN REPLACE(b.idname,'/r','')
ELSE REPLACE(b.idname,'d/','')
END AS idname
from @tbl2 a
left join @tbl1 b
on a.id=b.id
outer apply (select count(*) AS RecCnt
from @tbl2
where rowid=a.rowid
and b.idname IS NULL)c
order by rowid[/code]

didnt understand on what basis you decide values to be returned for idname in cases where its NULL though.

hai
Yak Posting Veteran

84 Posts

Posted - 2008-11-12 : 10:59:48

Hi Visak,

How can I assign the max(c.RecCnt) to the same rowid?

From your query:
pk rowid id RecCnt idname
1 1 3 0 r-03
2 1 2 0 r-02
3 1 3 0 r-03
8 1 3 0 r-03
5 1 3 0 r-03
6 2 2 0 r-02
7 2 2 0 r-02
12 2 2 0 r-02
13 2 0 5 NULL
4 2 2 0 r-02
9 4 2 0 r-02
10 4 1 0 r-01
11 4 0 3 NULL

I need to do this:

pk rowid id RecCnt idname
1 1 3 0 r-03
2 1 2 0 r-02
3 1 3 0 r-03
8 1 3 0 r-03
5 1 3 0 r-03
6 2 2 5 r-02
7 2 2 5 r-02
12 2 2 5 r-02
13 2 0 5 NULL
4 2 2 5 r-02
9 4 2 3 r-02
10 4 1 3 r-01
11 4 0 3 NULL
----
Again, thank you for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 11:07:45

[code]select a.pk, a.rowid, a.id,c.RecCnt,
CASE WHEN COALESCE(c.RecCnt,0)>0 THEN REPLACE(b.idname,'/r','')
ELSE REPLACE(b.idname,'d/','')
END AS idname
from @tbl2 a
left join @tbl1 b
on a.id=b.id
outer apply (select count(*) AS RecCnt
from @tbl2 a1
left join @tbl1 b1
on a1.id=b1.id
where a1.rowid=a.rowid
and b1.idname IS NULL)c
order by rowid[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources