Grabbing data based on conditions

I’m building results from 3 tables that are linked together by an Item ID but which table that ID refers to depends on the Activity ID in Main Table:

Main Table
-----------------
Activity ID
Item ID

Activity 1 Table
-----------------------
Item ID
Item Description

Activity 2 Table
-----------------------
Item ID
Item Description

Rows from Main Table could look like this…

Activity ID - Item ID
------------------------------
Activity A - 347
Activity B - 23
Activity A - 48

I’d like for them to look like this…

Activity ID - Item ID - Item Description
------------------------------------------------------------------
Activity 1 - 347 - A description for Item 347 in table Activity 1
Activity 2 - 23 - A description for Item 23 in table Activity 2
Activity 1 - 48 - A description for Item 48 in table Activity 1

I’d like to grab the item descriptions depending on their activity because a row with Item ID 45 (for example) could exist in both Activity A and Activity B tables.

Hope this makes sense. Thanks!


SELECT m.ActivityId, m.ItemId, a1.ItemDescription
FROM MainTable m
JOIN Activity1Table a1 ON a1.ItemId = m.ItemId
UNION ALL
SELECT m.ActivityId, m.ItemId, a2.ItemDescription
FROM MainTable m
JOIN Activity2Table a2 ON a2.ItemId = m.ItemId