SQL Help needed!

[xospec1alk]

So I have 3 different SQL queries that return bits of data.

What i need to happen is that i want to take out the ClientID part, and have it return to me ALL the data in one table, instead of making 3 separate queries.

Obviosuly i'm still a sql noob, and i didn't create these tables. So i have no clue where to start.

any help is appreciated. thx!

the problem here is that each of the tables needs the LookupKey in order to get the description, and I don't know how to circumvent that.

 

[MrChad]

select
(select Description from Lookup where LookupKey = 'ClientStyle' and Value = a.ClientStyle) "ClientStyle",
(select Description from Lookup where LookupKey = 'ClientType' and Value = a.ClientType) "ClientType",
(select Description from Lookup where LookupKey = 'Broker' and Value = a.Broker) "Broker"
from
Customer a,
Category b
where
a.Category = b.Category and
a.ClientID = 653525

... and be sure to beat that database designer with a stick

 

[MrChad]

Also, it seems like the join with the Category table is unnecessary, but maybe I'm missing something.

 

[xospec1alk]

Originally posted by: MrChad
select
(select Description from Lookup where LookupKey = 'ClientStyle' and Value = a.ClientStyle) "ClientStyle",
(select Description from Lookup where LookupKey = 'ClientType' and Value = a.ClientType) "ClientType",
(select Description from Lookup where LookupKey = 'Broker' and Value = a.Broker) "Broker"
from
Customer a,
Category b
where
a.Category = b.Category and
a.ClientID = 653525

... and be sure to beat that database designer with a stick

Thanks Chad, im trying this out now, will update.

and im glad im not the only one that thinks this designer did something stupid =)


W00t! it works. thanks chad.

Now was this a "simple" thing anyone that a basic grasp of SQL should be familiar with? I spent the last 2 hours wrestling with this issue and couldn't figure it out for the life of me.

 

[MrChad]

It uses subqueries, which is a bit of an intermediate/advanced concept. Subquerying isn't the most efficient technique, but sometimes it's the only way to get the result you need.