Help me with the SELECT statement please ?

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  February 8, 2018 at 9:59 am

  #329201

  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'
*/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 8, 2018 at 10:57 am

  #1979073

  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'
*/

  
select
    m.*
from
    #member as m
where
    exists(select 1 from #tmpTbl as t where t.BeneficiaryId like m.MemberId + '%' and t.MatchingRowCountInMemberTbl > 1);

  

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  February 8, 2018 at 11:03 am

  #1979075

  Lynn Pettis - Thursday, February 8, 2018 10:57 AM

  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'
*/

  
select
    m.*
from
    #member as m
where
    exists(select 1 from #tmpTbl as t where t.BeneficiaryId like m.MemberId + '%');

  

  Lynn: I tried that .. No luck 🙁

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  February 8, 2018 at 11:06 am

  #1979076

  mw112009 - Thursday, February 8, 2018 11:03 AM

  Lynn Pettis - Thursday, February 8, 2018 10:57 AM

  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'
*/

  
select
    m.*
from
    #member as m
where
    exists(select 1 from #tmpTbl as t where t.BeneficiaryId like m.MemberId + '%');

  

  Lynn: I tried that .. No luck 🙁

  Odd... but this worked ....
  
select
m.*
from
#member as m
where  exists(select 1 from #TmpTbl as t where m.MemberId like t.BeneficiaryId + '%' );


• ZZartin

  SSC-Dedicated

  Points: 30920

  More actions

  February 8, 2018 at 11:12 am

  #1979077

  mw112009 - Thursday, February 8, 2018 11:03 AM

  Lynn Pettis - Thursday, February 8, 2018 10:57 AM

  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'
*/

  
select
    m.*
from
    #member as m
where
    exists(select 1 from #tmpTbl as t where t.BeneficiaryId like m.MemberId + '%');

  

  Lynn: I tried that .. No luck 🙁

  You can try this but the issue with doing text matching like this is it'll break if you ever have over lap between member ids and whatever is in that beneficiary id after the member id part.

  SELECT * FROM #tmpTbl
INNER JOIN #member ON CHARINDEX(#tmpTbl.BeneficiaryId, #member.MemberId) = 1 AND #tmpTbl.BeneficiaryId != #member.MemberId

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 8, 2018 at 1:29 pm

  #1979101

  My fault, read things backwards when I was writing the code.

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  February 8, 2018 at 2:45 pm

  #1979119

  Lynn Pettis - Thursday, February 8, 2018 1:29 PM

  My fault, read things backwards when I was writing the code.

  Thanks! Credit goes to you!