Including values from different databases in my resultset

[vcurry]

My problem is a more advanced use of the CASE function. Basically, depending upon one of the values in my resultset, I want to get a value from one of two different databases.

An illustration of the problem would be:

Suppose I have the query:

SELECT drd_id, drd_description FROM Widgets

Now, if drd_description is 1 I want to get a value from the database OLD_Widgets, if 2 then I want a value from the database NEW_Widgets. I tried using CASE along the lines of:

SELECT drd_id, Description = CASE drd_Description
WHEN 1 THEN SELECT DESCRIPTION FROM [Old_Widgets].dbo.description dcs WHERE dcs.id = drd_description
WHEN 2 THEN SELECT DESCRIPTION FROM [New_Widgets].dbo.description dcs WHERE dcs.id = drd_description
END

Unfortnately, it would appear that CASE doesn't support the SELECT statement within it. Does anyone know how I can achieve a similar result?

 

[ClaireHCM]

case cant be used with select statement.

You can write user defined function stead.

 

[snag56]

Perhaps, try something like....
SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.[id] = table2.[id]
LEFT OUTER JOIN table3
ON table1.[id] = table3.[id]
WHERE COALESCE(table2.[id], table3.[id]) = table1.[id]

I used fictitious tables, but hopefully, you get the point.

 

[vcurry]

Thanks guys. The COALESCE funtion worked a treat; I joined up all the tables, and then just use the record that isn't null:

SELECT
wdg_description,
OriginationFactory = COALESCE(new_Factory, old_factory)
FROM Widgets
LEFT OUTER JOIN oldDB.dbo.WidgetFactories old on wdg_factory_id = old.Fact_id
LEFT OUTER JOIN newDB.dbo.WidgetFactories new on wdg_factory_id = new.Fact_id