Joining different datasets with parameters

Hi all,

lets say i have one table with colour ids and colour names, and another table with broad-leafed trees and different colour id fields for root, leafs and trunk:

Table colours:
integer field colour_id, varchar field colour_name
Table trees:
varchar field tree_type, integer field leaf_colour_id,
integer field trunk_colour_id, integer field root_colour_id

one way to get the fields would be:

select T.tree_type, C1.colour_name as leaf_colour_name,
C2.colour_name as trunk_colour_name, C3.colour_name as root_colour_name
from trees as T
inner join colours as C1 on C1.colour_id = leaf_colour_id
inner join colours as C2 on C2.colour_id = trunk_colour_id
inner join colours as C3 on C3.colour_id = root_colour_id;

But i would like to do that with some kind of inline function like:
colourName := select colour_name from colours where colour_id = @.colourId;
and then:
select tree_type, colourName(leaf_colour_id), colourName(trunk_colour_id), colourName(root_colour_id) from trees;

Is that possible? Any comments welcome.

Thanks,
haraldIn Sql Server, you can create a User Defined Function (Scalar) which will produce this. It will make code more readable but will slow it down a bit.
See the templates in SQL Server, and this article.
It would be something like:
CREATE FUNCTION fnGetColourName(@.ColourID as int)
RETURNS int
as
BEGIN
DECLARE @.Value as int
SET @.Value = (SELECT MAX(Colour_name)
FROM Colours
WHERE colour_id = @.ColourID
END
RETURN @.Value
SELECT dbo.fnGetColourName(leaf_colour_id) as leafColour
FROM trees
Alternatively, you can use the Custom Code section of the report to create a function that will do the same thing, see examples from Bryant Likes's blog here.|||

Hi wavemash,

thanks for your reply, now my sql statement is more readable

Thanks,
harald