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?
13 Answers
You'll want to use the WHERE EXISTS syntax instead.
SELECT *
FROM table1
WHERE EXISTS (SELECT *
FROM table2
WHERE Lead_Key = @Lead_Key
AND table1.CM_PLAN_ID = table2.CM_PLAN_ID
AND table1.Individual_ID = table2.Individual_ID)
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
WARNING ABOUT SOLUTIONS:
MANY EXISTING SOLUTIONS WILL GIVE THE WRONG OUTPUT IF ROWS ARE NOT UNIQUE
If you are the only person creating tables, this may not be relevant, but several solutions will give a different number of output rows from the code in question, when one of the tables may not contain unique rows.
WARNING ABOUT PROBLEM STATEMENT:
IN WITH MULTIPLE COLUMNS DOES NOT EXIST, THINK CAREFULLY WHAT YOU WANT
When I see an in with two columns, I can imagine it to mean two things:
- The value of column a and column b appear in the other table independently
- The values of column a and column b appear in the other table together on the same row
Scenario 1 is fairly trivial, simply use two IN statements.
In line with most existing answers, I hereby provide an overview of mentioned and additional approaches for Scenario 2 (and a brief judgement):
EXISTS (Safe, recommended for SQL Server)
As provided by @mrdenny, EXISTS sounds exactly as what you are looking for, here is his example:
SELECT * FROM T1
WHERE EXISTS
(SELECT * FROM T2
WHERE T1.a=T2.a and T1.b=T2.b)
