Query

Dear All
I have a query is:-
select
A.AllocatorId,a.CustomTypeId,a.CustomId,a.ScopeTypeId,a.ScopeId,a.ActivityTy
peId,a.PriorityId
from allocation a
Out put is
AllocatorId CustomTypeId CustomId ScopeTypeId ScopeId ActivityTypeId
PriorityId
-- -- -- -- -- --
--
1 456 2 38 154 20
151
and Another Query for corresponding ids
select LookupID,LookupTypeID,DisplayValue
from lookup where LookupID in ('154','151','456',2)
Out put is
LookupID LookupTypeID DisplayValue
-- -- ----
151 63 Low
154 38 Select Few Compay
456 106 UserID
2 10 Kolkatta
Now i would like to have output like this:-
1 456 2 Kolkatta 38 154 Select Few Compay 20 151 Low
I am using one query for it:
Query is
select Y.AllocatorId,Y.CustomTypeId,Y.CustomId,l.displayvalue as
CustomIdvalue
,Y.ScopeTypeId,Y.ScopeId,Y.ScopeidValue,Y.ActivityTypeId,Y.PriorityId,Y.Prio
rityIdValue
from
(select
x.AllocatorId,x.CustomTypeId,x.CustomId,x.ScopeTypeId,x.ScopeId,x.ScopeidVal
ue,x.ActivityTypeId,
x.PriorityId, l.displayValue as PriorityIdValue
from
(select
A.AllocatorId,a.CustomTypeId,a.CustomId,a.ScopeTypeId,a.ScopeId,l.DisplayVal
ue as scopeidValue,
a.ActivityTypeId,a.PriorityId
from allocation a
left join lookup l
on l.LookupID=a.ScopeId and l.LookupTypeID =a.Scopetypeid) X
left join lookup l
on l.LookupID=x.PriorityId ) Y
left join lookup l
on l.LookupID=Y.CustomId
Now My question is:-
Is ther any other optimized way to get this output.
thank & regards
Manoj kumarThe main problem is that your schema design is not normalized. You have 3
different columns referencing the same table. The best way to improve this
would be to have a separate lookup table for each type of id. You would the
n
have a very simple query.
If you don't have control over the schema, I think your query is a good way
to do it.
The only alternative way that I can think of, which seems to look simpler
but would perform the same is something like (I'm a little at the
purpose of the lookuptypeid since you use it once and not other times, so I
am going to skip it) Hopefully this gives you an idea:
select a.AllocatorId,
a.CustomTypeId,
a.CustomId,
l3.displayvalue as CustomIdvalue,
a.ScopeTypeId,
a.ScopeId,
l1.DisplayValue as ScopeidValue,
a.ActivityTypeId,
a.PriorityId,
l2.DisplayValue as PriorityIdValue
from allocation a
left join lookup l1
on l1.LookupID=a.ScopeId
left join lookup l2
on l2.LookupID=a.PriorityId
left join lookup l3
on l3.LookupID=a.CustomId
HTH
"Manoj" wrote:

>
> Dear All
> I have a query is:-
> select
> A.AllocatorId,a.CustomTypeId,a.CustomId,a.ScopeTypeId,a.ScopeId,a.Activity
TypeId,a.PriorityId
> from allocation a
> Out put is
> AllocatorId CustomTypeId CustomId ScopeTypeId ScopeId ActivityTypeI
d
> PriorityId
> -- -- -- -- -- --
-
> --
> 1 456 2 38 154 20
> 151
> and Another Query for corresponding ids
> select LookupID,LookupTypeID,DisplayValue
> from lookup where LookupID in ('154','151','456',2)
> Out put is
> LookupID LookupTypeID DisplayValue
> -- -- ---
-
> 151 63 Low
> 154 38 Select Few Compay
> 456 106 UserID
> 2 10 Kolkatta
> Now i would like to have output like this:-
> 1 456 2 Kolkatta 38 154 Select Few Compay 20 151 Low
> I am using one query for it:
> Query is
> select Y.AllocatorId,Y.CustomTypeId,Y.CustomId,l.displayvalue as
> CustomIdvalue
> ,Y.ScopeTypeId,Y.ScopeId,Y.ScopeidValue,Y.ActivityTypeId,Y.PriorityId,Y.P
riorityIdValue
> from
> (select
> x.AllocatorId,x.CustomTypeId,x.CustomId,x.ScopeTypeId,x.ScopeId,x.ScopeidV
alue,x.ActivityTypeId,
> x.PriorityId, l.displayValue as PriorityIdValue
> from
> (select
> A.AllocatorId,a.CustomTypeId,a.CustomId,a.ScopeTypeId,a.ScopeId,l.DisplayV
alue as scopeidValue,
> a.ActivityTypeId,a.PriorityId
> from allocation a
> left join lookup l
> on l.LookupID=a.ScopeId and l.LookupTypeID =a.Scopetypeid) X
> left join lookup l
> on l.LookupID=x.PriorityId ) Y
> left join lookup l
> on l.LookupID=Y.CustomId
>
> Now My question is:-
> Is ther any other optimized way to get this output.
> thank & regards
> Manoj kumar
>

qualifying table variables

hi all!

I am using table variables instead of creating a temp table because it seems to be faster

But now I need qualify the table variable so I can join it with another table having a field with same name of a field from the table variable. U know if I can do that?

ex: with temp table

create table #tmp... (F1...)
#tmp.f1

with table variable

declare @.temp table(...
@.table.f1 - can´t do it

the first question is if I can join the table variable with another table and how to do that qualifying the variable table, that is, putting the name of the var temp with the field, because the other table has a field with same name

thank to all and happiness for all 2004Hi all

I think I found the solution

It is not possible to qualify the talble variable because it is not part of a persistent table, so I cant assing table variables,

I saw it in

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_66w5.asp

it is the same as in sql 2000 BOL

thanks. so I have to use a temp table to qualify it if I want to join the table. My thinking is that I can join table variables

hope it is useful to u|||Hi all!

Thanks for having the time to read it

I was wrong and found the solution.

I can join and qualify a table var, only aliasing the table as in

insert into @.table... join temp.field...
from @.vartemp temp

--

here temp is the alias of @.vartemp, so I can use this alias instead of @.vartemp when, for example, there are two fields with same name in two tables, and one is @.vartemp

Now, once solved it, my problem is with UPDATETEXT. I am updating a text field and need to qualify the @.vartable in order to update it using a pointer to the text field. Since there are not any FROM clause as above, I dont know where to qualify the @.vartable (tried qualifying in DECLARE, but cant do it there)

UPDATETEXT @.vartemp.pointer - cant use @.vartemp, need an alias, but where to give the alias?

Thanks all!