How do I search/query multiple tables to return a common result?

I'm attempting to build a search stored procedure. I want to search multiple tables and return anything relevant to the application. Right now I'm working with two tables. dbo.Media has 5 records in it, and dbo.Sites has 1 record in it. When I run my query I get 5 results returned but all the row data is from my 1 record dbo.Sites table. Here's my SQL

SELECT  
CASE
    WHEN D.Id IS NOT NULL THEN D.Id
    WHEN M.Id IS NOT NULL THEN M.Id             
END AS Id,
CASE
    WHEN D.Id IS NOT NULL THEN D.Name
    WHEN M.Id IS NOT NULL THEN M.Title
END AS Title,
CASE
    WHEN D.Id IS NOT NULL THEN LEFT (D.[Description], 100)
    WHEN M.Id IS NOT NULL THEN LEFT (M.[Description], 100)
END AS Content,
CASE
    WHEN D.Id IS NOT NULL THEN D.WebUserId
开发者_如何学编程    WHEN M.Id IS NOT NULL THEN M.WebUserId
    ELSE ''
END AS WebUserId

FROM dbo.Sites D, dbo.Media M

WHERE (D.Status = 1 AND D.Name LIKE '%wre%') OR (M.Status = 1 AND M.Title LIKE '%wre%')

SELECT Id, Name as Title, LEFT ([Description], 100) as Content, WebUserId 
FROM dbo.DiveSites
WHERE Status = 1 
    AND Name LIKE '%wre%'
UNION ALL    
SELECT Id, Title, LEFT ([Description], 100), WebUserId 
FROM dbo.Media
WHERE Status = 1 
    AND Title LIKE '%wre%'