February 23, 2010 at 11:02 am
Hi is there any another way i can rewrite this below query. I was joining 3 coloumns from #temp5
with 1 coloumn in NIC.What i want in the result set is ...i need to select those rows ..if any of the coloumns from #temp5 matches with coloumn ICD from NIC.......
SELECT [Number],R=count([Number]),paid=sum([PAID])
FROM #temp5 R7
INNER JOIN [dbo].[Nic] T1
ON( RTRIM(LTRIM(R7.[DG1]))=T1.[Icd]
or RTRIM(LTRIM(R7.[DG2]))=T1.[Icd]
or RTRIM(LTRIM(R7.[DG3]))=T1.[Icd] )
WHERE T1.[TIER]=1
GROUP BY [RID]
Thanks
February 23, 2010 at 11:36 am
First a critique
1. Temp table names have no meaning (might just be example).
2. DG1, DG2, and DG3 should have been trimmed when they were inserted into #temp5.
Suggestions/Issues
1. You cannot group by a field not in the SELECT list so RID isn't going to work.
2. This seems to be more of an EXISTS case, you don't have a key to join on really..
This is as close as I can really take you.
SELECT [Number],R=count([Number]),paid=sum([PAID])
FROM #temp5 R7
WHERE EXISTS ( SELECT 'X'
FROM [dbo].[Nic] T1
WHERE T1.[TIER]=1
AND ( R7.[DG1]=T1.[Icd] OR R7.[DG2]=T1.[Icd] OR R7.[DG3]=T1.[Icd] ) )
GROUP BY [Number]
CEWII
February 24, 2010 at 3:30 am
Elliott W (2/23/2010)
First a critique1. Temp table names have no meaning (might just be example).
2. DG1, DG2, and DG3 should have been trimmed when they were inserted into #temp5.
Suggestions/Issues
1. [highlight=#ffff11]You cannot group by a field not in the SELECT list so RID isn't going to work.[/highlight]
2. This seems to be more of an EXISTS case, you don't have a key to join on really..
This is as close as I can really take you.
SELECT [Number],R=count([Number]),paid=sum([PAID])
FROM #temp5 R7
WHERE EXISTS ( SELECT 'X'
FROM [dbo].[Nic] T1
WHERE T1.[TIER]=1
AND ( R7.[DG1]=T1.[Icd] OR R7.[DG2]=T1.[Icd] OR R7.[DG3]=T1.[Icd] ) )
GROUP BY [Number]
CEWII
you can group by a column which is not in the select list.
But you cannot have [Number] in the select list as it is neither aggregated nor included in the group by column list
February 24, 2010 at 6:07 am
Using UNPIVOT would help here:
declare @temp5 table (T1 int, T2 int, T3 int)
insert @temp5
select 1,2,3
union all select 3,4,5
union all select 5,6,7
declare @Nic table (Id int)
insert @Nic
select 1
union all select 2
union all select 3
union all select 4
union all select 6
;with cte as (
select Value_Col, Value_Act
from (select T1,T2,T3
from @temp5) sel
unpivot (Value_Act for Value_Col in (T1,T2,T3)) unpvt
)
select * from cte
join @Nic n
on cte.Value_Act = n.Id
You need to be careful with it though - index choice and limiting the number of rows produced are vital...
Iain
February 24, 2010 at 8:10 am
Fazalul Haque (2/24/2010)
Elliott W (2/23/2010)
Suggestions/Issues1. [highlight=#ffff11]You cannot group by a field not in the SELECT list so RID isn't going to work.[/highlight]
CEWII
you can group by a column which is not in the select list.
But you cannot have [Number] in the select list as it is neither aggregated nor included in the group by column list
I'm not sure I see your point, RID is neither selected or aggregated. And I don't think it falls into any of the exceptions in BOL. I would expect to get the error about not being in the select list. Can you provide an example for my adled brain..
CEWII
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply