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?

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

Why use WHERE EXISTS or DERIVED TABLES when you can just do a normal inner join:

SELECT t.*
FROM table1 t
INNER JOIN CRM_VCM_CURRENT_LEAD_STATUS s
ON t.CM_PLAN_ID = s.CM_PLAN_ID
AND t.Individual_ID = s.Individual_ID
WHERE s.Lead_Key = :_Lead_Key

If the pair of (CM\_PLAN\_ID, Individual\_ID) isn't unique in the status table, you might need a SELECT DISTINCT t.* instead.

I founded easier this way

Select *
from table1
WHERE (convert(VARCHAR,CM_PLAN_ID) + convert(VARCHAR,Individual_ID))
IN
(
Select convert(VARCHAR,CM_PLAN_ID) + convert(VARCHAR,Individual_ID)
From CRM_VCM_CURRENT_LEAD_STATUS
Where Lead_Key = :_Lead_Key
)

Hope this help :)

Simple and wrong way would be combine two columns using + or concatenate and make one columns.

Select *
from XX
where col1+col2 in (Select col1+col2 from YY)



This would be offcourse pretty slow. Can not be used in programming but if in case you are just querying for verifying something may be used.

If you want for one table then use following query

SELECT S.*
FROM Student_info S
INNER JOIN Student_info UT
ON S.id = UT.id
AND S.studentName = UT.studentName
where S.id in (1,2) and S.studentName in ('a','b')

and table data as follow

id|name|adde|city
1 a ad ca
2 b bd bd
3 a ad ad
4 b bd bd
5 c cd cd

Then output as follow



id|name|adde|city
1 a ad ca
2 b bd bd

select * from tab1 where (col1,col2) in (select col1,col2 from tab2)

**Note:**
Oracle ignores rows where one or more of the selected columns is NULL. In these cases you probably want to make use of the [NVL][1]-Funktion to map NULL to a special value (that should not be in the values);

select * from tab1
where (col1, NVL(col2, '---') in (select col1, NVL(col2, '---') from tab2)


[1]:

[To see links please register here]

We can simply do this.

select *
from
table1 t, CRM_VCM_CURRENT_LEAD_STATUS c
WHERE t.CM_PLAN_ID = c.CRM_VCM_CURRENT_LEAD_STATUS
and t.Individual_ID = c.Individual_ID

Query:

select ord_num, agent_code, ord_date, ord_amount
from orders
where (agent_code, ord_amount) IN
(SELECT agent_code, MIN(ord_amount)
FROM orders
GROUP BY agent_code);

above query worked for me in mysql.
refer following link -->

[To see links please register here]