SQL Join

[adimulam]

I am trying to inner join on 2 tables. But when I was getting ready to write "ON caluse, I realised that only part of the 2nd colum (from 2nd table) matches the first column of the first table. How can I get the matching rows? Help is appreciated.

 

[gmmastros]

That will depend on what the data looks like. You may not realize it, but you can use functions in the on clause. It will definetly hurt performance, but if that's what you need to do to get the data, then so be it.

Ex;

Select *
From   Table1
       Inner Join Table2
         On Table1.FirstName = Left(Table2.FullName, Len(Table1.FirstName))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[adimulam]

Matching value may not be in the begining. It can be anywhere in the column. For example, if the value of the Table1.Col1 is "TEST", the value of Table2.Col1 can be "BEST TEST BEST".

 

[gmmastros]

OK.

Select *
From   Table1
       Inner Join Table2
         On Table2.Col1 Like '%' + Table1.col1 + '%'

Here's an example that uses table variables.

Declare @T1 Table(id int, data varchar(20))
Declare @T2 Table(id int, data varchar(20))

Insert Into @T1 Values(1, 'test')
Insert Into @T1 Values(2, 'yellow')

Insert Into @T2 Values(1, 'best test best')
Insert Into @T2 Values(2, 'Greatest')

Select *
From   @T1 T1
       Inner Join @T2 T2
         On T2.Data Like '%' + T1.Data + '%'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom