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
>