0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

;WITH Contacts (ID) AS (
  SELECT 1 UNION ALL
  SELECT 2
  )
  , ContactClassifications (ContactID, ClassificationID) AS (
  SELECT 1, 'GUID1' UNION ALL
  SELECT 1, 'GUID2' UNION ALL
  SELECT 2, 'GUID1' 
  )  
SELECT * 
FROM   Contacts c 
       INNER JOIN ( 
          SELECT c.ID 
          FROM   Contacts c 
                 INNER JOIN ContactClassifications cc ON c.ID = cc.ContactID 
          WHERE  cc.ClassificationID IN ('GUID1', 'GUID2') 
          GROUP BY 
                 c.ID 
          HAVING COUNT(DISTINCT cc.ClassificationID) = 2
       ) cc ON cc.ID = c.ID

fork query

edited jun 5 12

created jun 5 12 anonymous