CODEWITHSUNDEEP
sqlsql-server
Mark
Mark

Reputation: 531

Writing query with multiple varying conditions on data subset

I am writing a query that searches through each MailID and selects ActivityId 1 that has an associated ActivityId 3 but not an associated ActivityId 7.

Here is a sample result set:

MailID | ActivityId
==============
155311406 | 1
154733588 | 3
154733588 | 1
154733588 | 7
154723283 | 1
154699790 | 1
154692365 | 7
154692365 | 1
154692365 | 3
154685546 | 7
154685546 | 1
154685546 | 3
154093013 | 1
153921107 | 3
153921107 | 7
153921107 | 1
153920994 | 3
153920994 | 1
153920932 | 7
153920932 | 3
153920932 | 1

I have attempted the query but I am not getting the correct output:

SELECT MailId, ActivityId
INTO #temp
FROM Mail WITH(NOLOCK)
WHERE ActivityId IN (1, 3, 7)


SELECT MailID
FROM Mail M
WHERE EXISTS (SELECT 1 FROM #temp T WHERE T.vNettId = M.vNettID AND T.ActivityId = 3)
AND NOT EXISTS (SELECT 1 FROM #temp T WHERE T.vNettId = M.vNettID AND T.ActivityId = 7)
AND M.ActivityId = 1

Any help with this query would be greatly appreciated

Thanks!

Upvotes: 0

Views: 59

Answers (3)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

You can't get any resultset from your query, because the field vNettID that seems to be used to associate MailIDs is not contained in your #temp table.

How about another query without a temporary table:

WITH T (vNettID) AS (
  SELECT vNettID
  FROM Mail
  WHERE ActivityID IN (3, 7)
  GROUP BY vNettID
  HAVING SUM(CASE ActivityID WHEN 7 THEN 1 ELSE 0 END) = 0
)
SELECT M.MailID
FROM Mail M 
  INNER JOIN T ON M.vNettID = T.vNettID
WHERE M.ActivityID = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can write this as:

SELECT MailId
FROM Mail 
WHERE ActivityId IN (1, 3, 7)
GROUP BY MailId
HAVING MIN(ActivityId) = 1 AND
       MAX(ActivityId) = 3;

This is not super-generalizable. A more general method is:

SELECT MailId
FROM Mail 
WHERE ActivityId IN (1, 3, 7)
GROUP BY MailId
HAVING SUM(CASE WHEN ActivityId = 1 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN ActivityId = 3 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN ActivityId = 7 THEN 1 ELSE 0 END) = 0;

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can do the query using group by and having.

SELECT MailId
FROM Mail 
WHERE ActivityId IN (1, 3, 7)
GROUP BY MailId
HAVING COUNT(DISTINCT CASE WHEN ActivityId IN (1,3) THEN ActivityId END)=2
AND COUNT(DISTINCT CASE WHEN ActivityId = 7 THEN ActivityId END)=0

Upvotes: 1

Related Questions