sql join history table to active table SSRS report

Question

I'm trying to pull results from a database (sql server 2005) which takes 4 tables: Subscriber S, Member M, ClaimLines L, ClaimHistoryLines H

Query is as follows:

select S.SBSB_ID, M.MEME_NAME,
(CASE L.CLCL_ID WHEN '' THEN H.CLCL_ID ELSE L.CLCL_ID END) AS CLAIM_ID
FROM CMC_CDDL_CL_LINE L, CMC_MEME_MEMBER M LEFT OUTER JOIN CMC_CLDH_DEN_HIST H 
ON H.MEME_CK = M.MEME_CK, CMC_SBSB_SUBSC S
WHERE 
S.SBSB_ID = '120943270' AND
L.MEME_CK = M.MEME_CK AND
M.SBSB_CK = S.SBSB_CK

This query successfully pulls in the result rows from the ClaimLines L table but no results from the History table are shown. I'm not sure how to do this, any sql experts out there that can help would be great. -Thanks!

Answer 1

CMC_CDDL_CL_LINE L, CMC_MEME_MEMBER M LEFT OUTER JOIN CMC_CLDH_DEN_HIST H

don't mix obsolete implied join syntax with left join. They don't play well together. Use the correct ANSII standard join syntax. Infact stop using the obsolete syntax altogether.

Answer 2

This was a very silly mistake on my part. I neglected to think of using a UNION which solved my problem and allowed me to pull from both places into a single results set without creating massive duplicate rows. Glad someone pointed out the proper way to write sql using ANSII standards.

select S.SBSB_ID, M.MEME_NAME,
L.CLCL_ID AS CLAIM_ID
FROM
CMC_SBSB_SUBSC S INNER JOIN CMC_MEME_MEMBER M ON S.SBSB_CK = M.SBSB_CK
INNER JOIN CMC_CDDL_CL_LINE L ON L.MEME_CK = M.MEME_CK
WHERE 
S.SBSB_ID = '120943270'
UNION
select S.SBSB_ID, M.MEME_NAME,
H.CLCL_ID AS CLAIM_ID
FROM
CMC_SBSB_SUBSC S INNER JOIN CMC_MEME_MEMBER M ON S.SBSB_CK = M.SBSB_CK
INNER JOIN CMC_CLDH_DEN_HIST H ON H.MEME_CK = M.MEME_CK
WHERE 
S.SBSB_ID = '120943270'