sqlsql-server

SQL WHERE.. IN clause multiple columns

I need to implement the following query in SQL Server:

SELECT *
FROM table1
WHERE (CM_PLAN_ID, Individual_ID)
IN (
    SELECT CM_PLAN_ID, Individual_ID
    FROM CRM_VCM_CURRENT_LEAD_STATUS
    WHERE Lead_Key = :_Lead_Key
)

But the WHERE..IN clause allows only 1 column. How can I compare 2 or more columns with another inner SELECT?

Solution

  • You can make a derived table from the subquery, and join table1 to this derived table:

    SELECT *
FROM table1
LEFT JOIN 
(
   SELECT CM_PLAN_ID, Individual_ID
   FROM CRM_VCM_CURRENT_LEAD_STATUS
   WHERE Lead_Key = :_Lead_Key
) table2 ON 
    table1.CM_PLAN_ID = table2.CM_PLAN_ID
    AND table1.Individual = table2.Individual
WHERE table2.CM_PLAN_ID IS NOT NULL