SQL Server left join with conditionals not giving me results I want

Query first, and then question:

SELECT  DISTINCT p.postID, p.postGUID, p.postTitle, p.postTypeID, p.sequence, m.firstname, m.lastname, pt.postTypeName, mc.acceptRejectDate
FROM    post p
INNER JOIN member m ON p.memberGUID = m.memberGUID
INNER JOIN postType pt ON p.postTypeID = pt.postTypeID
LEFT JOIN masterClass mc ON (p.postGUID = mc.postGUID AND mc.isMemberPrivate = 0 AND mc.status = 2 AND mc.acceptRejectDate IS NOT NULL)
WHERE   p.postTitle LIKE '%five%'
AND p.isActive = 1
ORDER BY    p.postTypeID, p.sequence, mc.acceptRejectDate

What I'm trying to do here is grab all results from the "posts" table that have isActive = 1 and the title includes "five." Easy enough.

Some of the results also have an association to the masterClass table. For those results I only want to include them if isMemberPrivate is zero, status is 2, and there is an acceptRejectDate.

I thought this would be easy enough, but when I run the query开发者_运维百科 I get results including some posts that don't meet the master class join criteria. And, there are a few results that are displaying as having null values but clearly don't when I look at the raw data.

Is there anything in this query that looks wrong and would cause my results to be incorrect?

From reading your question, I believe you want to include all results that either have no record in the masterClass table, or do have records in the masterClass table that meet your criteria. If you move your criteria down to the WHERE section, and add a check for records that did not match anything in the masterClass table, you should get what you want.

SELECT  DISTINCT p.postID, p.postGUID, p.postTitle, p.postTypeID, p.sequence, m.firstname, m.lastname, pt.postTypeName, mc.acceptRejectDate
FROM    post p
INNER JOIN member m ON p.memberGUID = m.memberGUID
INNER JOIN postType pt ON p.postTypeID = pt.postTypeID
LEFT JOIN masterClass mc ON p.postGUID = mc.postGUID
WHERE   p.postTitle LIKE '%five%'
AND p.isActive = 1
AND ( mc.postGUID is NULL OR 
     (mc.isMemberPrivate = 0 AND mc.status = 2 AND mc.acceptRejectDate IS NOT NULL)
    )
ORDER BY    p.postTypeID, p.sequence, mc.acceptRejectDate

Edit: Changed field looking for NULLs from mc.acceptRejectDate to mc.postGUID

Try this to clearly separate JOIN and filter conditions

...
LEFT JOIN
(
SELECT postGUID, acceptRejectDate
FROM masterClass
WHERE isMemberPrivate = 0 AND status = 2 AND acceptRejectDate IS NOT NULL
) mc ON p.postGUID = mc.postGUID
...