SQL 2005 - Usage of recursive query and Union

Below is an example which I implemented in this morning. Need to use the SQL to perform join among three tables

select c.CID, c.event, c.SID, c.TID, c.create_timestamp,

SUBSTRING(d.userid,1,4)+'XXXX'+SUBSTRING(d.userid,len(d.userid)-3,4) as mobile

from

(select a.CID, b.alias as event, a.SID, a.TID, a.create_timestamp

from XXXX..TxEvent as a join XXXX..campaigns as b

on a.success='0' and a.CID=b.campaignid) c

join XXX..MMS_Usage as d on c.SID=d.session_id and d.inbound='0'


union


select a.campaign_id as CID, b.alias as event, a.session_id as SID, a.TID, a.create_timestamp,

SUBSTRING(a.userid,1,4)+'XXXX'+SUBSTRING(a.userid,len(a.userid)-3,4) as mobile

from XXXX..Master_TxEvent as a join XXXX..campaigns as b

on a.state='0' and a.campaign_id=b.campaignid