Subquery problem
-
Hi,
I have a table that has a column Fieldname and a Column Code
I need to get a description for every Code. The problem is that the subquery I use varies with the value in the FieldName column.
I now have a query that looks like this:
SELECT
a.fieldname,
a.Code,
CASE a.fieldname
WHEN 'Thing1' then (Select b.Thing1 From Thing1 b Where b.Code = a.Code)
WHEN 'Thing2' then (Select b.Thing2 From Thing1 b Where b.Code = a.Code)
WHEN 'Thing3' then (Select b.Thing3 From Thing1 b Where b.Code = a.Code)
ELSE 0
END As Description
FROM
ATable a
Is it possible to change this SQL into something like this?
SELECT
a.fieldname,
a.Code,
Select b.Resolve(a.fieldname) From Resolve(a.Fieldname) b Where b.Code = a.Code) As Description
FROM
ATable a
Thanks,
Leon
-
2 things come to mind :
1 - Is it possible to merge the 3 lookup tables into one (if it makes sens)?
2 - You can make 3 derieved table with an exotic join :
Select A.col1, A.col2, case A.FieldName
When 'Thing1' THEN dt1.Code
When 'Thing2' THEN dt2.Code
When 'Thing3' THEN dt3.Code
ELSE ''
END
from ATableA cross join (Select id, code from things1) dt1 cross join (Select id, code from things2) dt2 cross join (Select id, code from things3) dt3 WHERE 1 = CASE
When A.FieldName = 'Thing1' AND dt1.Code = A.Code THEN 1
When A.FieldName = 'Thing2' AND dt2.Code = A.Code THEN 1
When A.FieldName = 'Thing3' AND dt3.Code = A.Code THEN 1
ELSE 0
END
I have never tested anything like this... I have no idea how fast this will perform.
-
I think you only need this :
SELECT
a.fieldname,
a.Code,
CASE a.fieldname
WHEN 'Thing1' then b.Thing1
WHEN 'Thing2' then b.Thing2
WHEN 'Thing3' then b.Thing3
ELSE 0
END As Description
FROM ATable a
left join Thing1 b -- maybe even use an inner join
on b.Code = a.Code
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Might be a better idea... after all
.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply