So basically I have three tables with three types of "things". Each of these tables have a column called "name". My fourth table contains references to these tables through an id field, only one of these tables will be related to the fourth table - I have produced the following SQL so the query has a name column which is populated by the related tables name.
WHEN thing1.name IS NOT NULL THEN thing1.name
WHEN thing2.name IS NOT NULL THEN thing2.name
WHEN thing3.name IS NOT NULL THEN thing3.name
END AS name,
FROM base_table AS base
LEFT OUTER JOIN thing1_table AS thing1 ON base.as_id = thing1.as_id
LEFT OUTER JOIN thing2_table AS thing2 ON base.bl_id = thing2.bl_id
LEFT OUTER JOIN thing3_table AS thing3 ON base.ld_id = thing3.ld_id
That query works, my problem is the following, I hope to use the query in a SSRS report with a parameter enabling a search against the name column (which the case statement populates).
I would like to query the returned "name" column from that query - I could populate a temporary table however I would like to know what the "proper" way is for this?