Query

30 Dec 2005 6:26 AM

Manoj

Dear All
I have a query is:-
select
A.AllocatorId,a.CustomTypeId,a.CustomId,a.ScopeTypeId,a.ScopeId,a.ActivityTypeId,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.PriorityIdValue
from
(select
x.AllocatorId,x.CustomTypeId,x.CustomId,x.ScopeTypeId,x.ScopeId,x.ScopeidValue,x.ActivityTypeId,
    x.PriorityId, l.displayValue as PriorityIdValue
from
(select
A.AllocatorId,a.CustomTypeId,a.CustomId,a.ScopeTypeId,a.ScopeId,l.DisplayValue 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

30 Dec 2005 3:16 PM

Ryan Powers

The 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 then
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 confused 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

Show quote

"Manoj" wrote:

>
>
> Dear All
> I have a query is:-
> select
> A.AllocatorId,a.CustomTypeId,a.CustomId,a.ScopeTypeId,a.ScopeId,a.ActivityTypeId,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.PriorityIdValue
> from
> (select
> x.AllocatorId,x.CustomTypeId,x.CustomId,x.ScopeTypeId,x.ScopeId,x.ScopeidValue,x.ActivityTypeId,
>     x.PriorityId, l.displayValue as PriorityIdValue
> from
> (select
> A.AllocatorId,a.CustomTypeId,a.CustomId,a.ScopeTypeId,a.ScopeId,l.DisplayValue 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
>