joining two tables using either like or in

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

General SQL Server Forums

New to SQL Server Programming

joining two tables using either like or in

Author

Topic

wendymackay
Starting Member

10 Posts

Posted - 2008-09-05 : 10:11:45

I've got two tables where the similar fields are an id field. No matter how I set up the query and join the 2 tables I still get no data. One table is "category" with the id field as CHAR but the entries in the fields are all numbers. The other table is "area" and the similar field to join is "id_string" which is VARCHAR but within this field the numbers are surrounded by a pipe, ie. '|24|' and '|1|3|18|'.

I've tried:
Select a.*
from category a, area b
where a.id like '%|id_string|%'

but this returns no data. I've tried:
select a.*
from category a, sarea b
where a.id in ('%id_string%')

but that gets no data either. The pipe is killing me and I'm not sure how to work around it or with it. Any suggestions

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-05 : 10:24:55

Select a.*
from category as a
inner join area as b on '|' + b.id_string + '|' LIKE '%|' + cast(a.id as varchar(11)) + '|%'

E 12°55'05.63"
N 56°04'39.26"

wendymackay
Starting Member

10 Posts

Posted - 2008-09-05 : 10:51:38

Thank you Peso - that did the trick. I would have never figured that one out! Thank you again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources