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)

No comments:

Post a Comment