Join on Field1 like '%Field2%'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join on Field1 like '%Field2%'

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2012-04-11 : 13:52:04

All,

I have 2 tables that I need to do a join on but the field I am joining on may or may not match exactly. So I need the join to be anywhere tbl1.Number is like tbl2.Number. How would I do this?

The only field that the two table might have in common is t1.[Pat# Id] and t2.[Accession #]. See below what I am trying to do.



SELECT  
       t1.[CPT]    
      ,t1.[Pat# Id]   
      ,t2.[Accession #]
  FROM [AdHoc].[dbo].[SJC] T1
  LEFT JOIN LIS_Data.dbo.Path_Way_Accessions T2 ON t1.[Accession #] LIKE '%t2.[Pat# Id]%'
  WHERE [Accession #] IS NOT NULL
  
  GROUP BY
       t1.[CPT]    
      ,t1.[Pat# Id]  
      ,t2.[Accession #]

Brian

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-11 : 15:02:14

Is the query not working as expected? Syntax-wise, it looks ok to me - the only thing I would suggest is to explicitly specify the table alias in the where clause as in

WHERE t1.[Accession #] IS NOT NULL

If you are not getting the results you are expecting, can you post some sample data along with expected output?

bconner
Starting Member

48 Posts

Posted - 2012-04-11 : 15:58:17

Here is some sample data

CPT Pat# Id Accession #
82106 SC11-000163-TX AP11-003849-DH
82106 SC11-000163-TX AP11-003850-AD
82106 SC11-000163-TX AP11-003850-DH
82106 SC11-000163-TX AP11-003851-AD
82106 SC11-000163-TX AP11-003851-DH

Brian

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-11 : 19:33:44

Not sure what the output you are looking for is. Also, with both tables having Pat#ID and Accession#, but the Pat#ID in one table matching with the Accession# in the second table somehow seems not quite right. Then again, I don't know your business logic, so that indeed may be what you want.

Anyhow, here is an example data set and a query. See if this will help you figure out what you might need to do:

CREATE TABLE #tmpAdhoc(CPT INT, PatId VARCHAR(32), Accession VARCHAR(32));
CREATE TABLE #tmpPathwayAccessions (PatId VARCHAR(32), Accession VARCHAR(32));

INSERT INTO #tmpAdhoc VALUES
(82106,'SC11-000163-TX','AP11-003849-DH'),
(82106,'SC11-000163-TX','AP11-003850-AD'),
(82106,'SC11-000163-TX','AP11-003850-DH'),
(82106,'SC11-000163-TX','AP11-003851-AD'),
(82106,'SC11-000163-TX','AP11-003851-DH');

INSERT INTO #tmpPathwayAccessions VALUES
( '003850','abcd'), ('004444','efgh');

SELECT t1.[CPT],
       t1.PatId,
       t2.Accession
FROM   #tmpAdhoc T1
       LEFT JOIN #tmpPathwayAccessions T2
            ON  t1.Accession LIKE '%' + t2.PatId + '%'
WHERE  t1.[Accession] IS NOT NULL

DROP TABLE #tmpAdhoc;
DROP TABLE #tmpPathwayAccessions;

Edit: One thing I hadn't noticed in your original query may be the cause of the problem. See the part I have marked in red above.

bconner
Starting Member

48 Posts

Posted - 2012-04-12 : 09:36:07

Sunitabeck
That worked! Thank you very much... I appreciate it.

Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources