SQL Server has left the conditions and does not give me the desired results

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

Hello, buddy!责编内容来自:Hello, buddy! (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » SQL Server has left the conditions and does not give me the desired results

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录