Selecting / Searching Multiple values from multiple tables , returning only the matched cases

Question

Dears Please help me pass multiple CPs to find the Process Ids from any one of the following table .

I want to do something like this :

declare @CP varchar(30)
Set @CP ='684980','123123','456456'

select ESuser.tb_pt_servicerequest.process_instance_id, ESuser.tb_pt_servicerequest.cpno
from
ESuser.tb_pt_servicerequest where ESuser.tb_pt_servicerequest.cpno in (@CP)

union all

select ESuser.tb_pt_additionalcp.process_instance_id, ESuser.tb_pt_additionalcp.cpno
from
ESuser.tb_pt_additionalcp where ESuser.tb_pt_additionalcp.cpno in (@CP)

OR like this

select * from XXXX, WWW, MMMM, KKK where CP_NO in ('123123','123321','123567')

Using this , But here I am unable to pass multiple cp number to get the returns : Now in this variable i can pass one value like '123123' and can get the results , but if i change the query to like in ('123123','123321') instead of =('123123'), I could not get the results . :(

declare @CP varchar(10)
Set @CP ='684980  '

select ESuser.tb_pt_servicerequest.process_instance_id, ESuser.tb_pt_servicerequest.cpno
from
ESuser.tb_pt_servicerequest where ESuser.tb_pt_servicerequest.cpno = @CP

union all

select ESuser.tb_pt_additionalcp.process_instance_id, ESuser.tb_pt_additionalcp.cpno
from
ESuser.tb_pt_additionalcp where ESuser.tb_pt_additionalcp.cpno = @CP

union all

select ESuser.tb_pt_new_addtionalcp.process_instance_id,         ESuser.tb_pt_new_addtionalcp.cpno
from
ESuser.tb_pt_new_addtionalcp where ESuser.tb_pt_new_addtionalcp.cpno = @CP

union all

select ESuser.tb_pt_vip_service_request.process_instance_id,
ESuser.tb_pt_vip_service_request.cpno
from
    ESuser.tb_pt_vip_service_request where ESuser.tb_pt_vip_service_request.cpno = @CP

union all

select
ESuser.tb_pt_vip_additional_cp.process_instance_id,ESuser.tb_pt_vip_additional_cp.cpno
from
ESuser.tb_pt_vip_additional_cp where ESuser.tb_pt_vip_additional_cp.cpno = @CP

union all

select ESuser.tb_pt_servicerequestbypass.process_instance_id, ESuser.tb_pt_servicerequestbypass.cpno
from
ESuser.tb_pt_servicerequestbypass where ESuser.tb_pt_servicerequestbypass.cpno = @CP

use wild card while using like keyword in SQL..example SELECT * FROM table, table1, table2 WHERE column LIKE '%contains%'. — ELITE
– ELITE, Commented Mar 13, 2016 at 11:18

Giorgos Betsos · Accepted Answer · 2016-03-13 11:18:47Z

1

You have to use a table variable. This is how you can declare it and populate it:

DECLARE @CP TABLE (CP_ID VARCHAR(10))
INSERT INTO @CP VALUES ('123123'),('123321'),('123567')

And this is how you can use it in your query:

SELECT ESuser.tb_pt_servicerequest.process_instance_id, 
       ESuser.tb_pt_servicerequest.cpno
FROM ESuser.tb_pt_servicerequest 
WHERE ESuser.tb_pt_servicerequest.cpno IN (SELECT CP_ID FROM @CP)

answered Mar 13, 2016 at 11:18

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Syed Umair Rizvi Over a year ago

Dear Thanks but its giving syntax error , saying incorrect syntax near ','. please help .

Giorgos Betsos Over a year ago

@Umair Which part? I think you have not copied the statements correctly.

Syed Umair Rizvi Over a year ago

Thanks for such a quick reply , you are amazing .. I am running both the statements together . here is my query . declare atCP table (CP_ID varchar(10)) insert into atCP values ('522452'),('522452'),('522452'),('522452') select ESuser.tb_pt_servicerequest.process_instance_id, ESuser.tb_pt_servicerequest.cpno from ESuser.tb_pt_servicerequest where ESuser.tb_pt_servicerequest.cpno in (select CP_ID from atCP) ERROR : Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ','.

Giorgos Betsos Over a year ago

@Syed You have not used the @ in atCP

Syed Umair Rizvi Over a year ago

no no dear @ in my code is at , as I was not able to comment :( here is my code declare @ CP table (CP_ID varchar(10)) insert into @ CP values ('522452'),('522452'),('522452'),('522452') select ESuser.tb_pt_servicerequest.process_instance_id, ESuser.tb_pt_servicerequest.cpno from ESuser.tb_pt_servicerequest where ESuser.tb_pt_servicerequest.cpno in (select CP_ID from @ CP) ERROR : Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ','.

|

Collectives™ on Stack Overflow

Selecting / Searching Multiple values from multiple tables , returning only the matched cases

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related