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)

Wednesday, August 07, 2013

WHERE clause with multiple AND operators for the same column


Here the developer wanted to pass n number of values for the MailAddress_Key column and return all the records which has all the values for the same MailGroup_FKey.

If values 6,7,1 passed, it should return all the records which satisfy that criteria if it's satisfied by individual MailGroup_Key(s). In the example MailGroup_FKey 2 and 3 both satisfy the criteria and it should return the relevant records of MailGroup_FKey 2 and 3

Here's the SQL

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