Joined on table, Field like joined field

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 2005 Forums

Transact-SQL (2005)

Joined on table, Field like joined field

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-04-24 : 08:46:55

Good day, i need help.

I'm joining on table but would like to retrun where the field like the joined field.

Main Field = 'IT'
Joined Field = FINANCE,IT,HR

inner join dbo.fcVendorMaster M
on M.CompanyCode = U.CompanyCode

where U.UserName =
and U.UserType like '%' + M.UserType + '%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 08:51:47

sorry not clear. can you explain with example?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-04-24 : 09:01:00

The table's field i'm joining on contains values 'IT,FINANCE,HR'
to the field that only has 'IT'

table1.field1 = 'IT'
table2.field2 = 'IT,FINANCE,HR'

now i would like to return the record based on a like
but can't seem to get it to work!

and table1.field1 like '%' + table2.field2 + '%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 09:15:21

post some data sample and explain

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-04-24 : 13:50:34

Hi,

Table1
Col1 Col2
IT Test1
HO Test2

Table2
Col1 Col2
IT,HR,Finance Dept1
HO,BA Dept2

On the whee clause like - must return Dept1 etc

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-24 : 14:11:38

Just try it and let me know whether this was your requirement or something else.

Declare @Test table
(Col1 varchar(50),
Col2 varchar(50))

Declare @TestData table
(Col1 varchar(50),
Col2 varchar(50))

Insert into @Test
select 'IT','Test1' union
select 'HO','Test2'

Insert into @TestData
select 'IT,HR,Finance','Dept1' union
select 'HO,BA','Dept2' union
select 'TestNo1,TestNo2','Dept3'

Select T2.Col2, T2.Col1 from
@TestData T2 inner join @Test T1 on T2.Col1 like '%' + T1.Col1 + '%'

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-24 : 14:15:00

[code]
SELECT *
FROM Table2 t2 LEFT JOIN Table1 t1 ON t2.Col1 LIKE '%'+t1.Col1+'%'
[/code]

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-04-24 : 14:16:04

lol, din refresh

Hope can help...but advise to wait pros with confirmation...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-25 : 02:17:29

slight change in sample data and see the change


Declare @Test table
(Col1 varchar(50),
Col2 varchar(50))


Declare @TestData table
(Col1 varchar(50),
Col2 varchar(50))



Insert into @Test
select 'IT','Test1' union all
select 'HO','Test2' union all
select 'ITES','Test3'

Insert into @TestData
select 'IT,HR,Finance','Dept1' union all
select 'HO,BA,ITES','Dept2' union all
select 'TestNo1,TestNo2','Dept3' 


--bohra/waterduck solution
Select t1.Col2,T2.Col2, T2.Col1 from 
@TestData T2 inner join @Test T1 on T2.Col1 like '%' + T1.Col1 + '%' 

--visakh soln
Select t1.Col2,T2.Col2, T2.Col1 from 
@TestData T2 inner join @Test T1 on ',' + T2.Col1 + ',' like '%,' + T1.Col1 + ',%' 


output
------------------------------------------------
--bohra/waterduck
Col2	Col2	Col1
Test1	Dept1	IT,HR,Finance
Test1	Dept2	HO,BA,ITES
Test2	Dept2	HO,BA,ITES
Test3	Dept2	HO,BA,ITES

--visakh
Col2	Col2	Col1
Test1	Dept1	IT,HR,Finance
Test2	Dept2	HO,BA,ITES
Test3	Dept2	HO,BA,ITES

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-04-25 : 04:39:49

Thank You All, silly mistake.
Syntax was correct, but columns wrong way around

table2.field2 like '%' + table1.field1 + '%'

Thanks :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-25 : 05:01:35

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources