mw112009 - Thursday, February 8, 2018 9:59 AM

Good Folks... My question is already in the code...
See the code ( the very last SQL statement ) 

Basically I am asking a question on how to use the LIKE operator with multiple values.


IF OBJECT_ID('tempdb..#tmpTbl') IS NOT NULL
DROP TABLE #tmpTbl;

IF OBJECT_ID('tempdb..#member') IS NOT NULL
DROP TABLE #member;

CREATE TABLE #member (
MemberId varchar(100), ID INT IDENTITY(1,1)
);

INSERT INTO #member ( MemberId )
Select '0001261781'
UNION
Select '0020166055'
UNION
Select '0020166055ABC'
UNION
Select '0001261781DEF'

INSERT INTO #member ( MemberId )
Select '0001261781'
UNION
Select '0020166055'

CREATE TABLE #tmpTbl (
BeneficiaryId varchar(100),
MatchingRowCountInMemberTbl int DEFAULT 0
);

INSERT INTO #tmpTbl( BeneficiaryId, MatchingRowCountInMemberTbl )
Select '0001261781', 2
UNION
Select '0020166055', 2

Select
M.*
FROM
#member M
INNER JOIN
#tmpTbl T ON ( M.MemberId = T.BeneficiaryId )
WHERE
T.MatchingRowCountInMemberTbl > 1
/*
I need the SQl statement modified so that it captures any MEMBERID in #member that will match the BeneficiaryID in #tmpTBL
So in other words, the SELECt stament should resturn the
'0020166055ABC','0001261781DEF'
*/