Friday, August 09, 2013

WHERE clause with multiple AND operators for the same column-[changed]

This relates to the earlier post. The developer wanted to eliminate those records if there are more than one MailGroup_FKey which satisfy the condition. So the SQL changed,

SELECT * 
FROM MailAddress_MailGroup
WHERE  MailGroup_FKey IN (
SELECT MailGroup_FKey FROM MailAddress_MailGroup
WHERE MailAddress_FKey IN (6,7) AND 
(SELECT COUNT(*) FROM (SELECT MailGroup_FKey  FROM MailAddress_MailGroup
WHERE MailAddress_FKey IN (6,7)
GROUP BY MailGroup_FKey
HAVING COUNT(*) = 2))=1
GROUP BY MailGroup_FKey
HAVING COUNT(*) = 2 AND COUNT(DISTINCT MailGroup_FKey)=1
) AND MailAddress_FKey IN (6,7)

No comments:

Post a Comment