Check if the a column content is part of another

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)

Check if the a column content is part of another

Author

Topic

vgarzon
Starting Member

11 Posts

Posted - 2010-09-30 : 14:28:09

Hi,
I'm having issues making a join of two tables.

Table1 is

Name varchar(64)
Route varchar (20)
Item char(2) (just 1 item per record i.e. "XX")

Table2 is

Benefit varchar(20)
Route varchar(20)
Items varchar(128) (many items per record i.e. "XY, XX, YY")

I need to make a report that shows the name from table1 and the benefits from table2, where they have the same route on both tables AND also -here's the complexity and the question- where the Item from table1 IS CONTAINED in table2. So I thought of


SELECT table1.Name, table2.Benefits
FROM table1 INNER JOIN table2
ON (table1.Route = table2.Route
AND table2.Items LIKE '%' + table1.Item + '%' )

But the "LIKE" comparison with wildcards does not work when comparing two columns. It only works when comparing a column with a string or variable.

Any thought on how to do this?

Thanks in advance!

Victor

da42
Starting Member

3 Posts

Posted - 2010-09-30 : 14:52:49

Try charindex(Table1.Item,Table2.Items) > 0

vgarzon
Starting Member

11 Posts

Posted - 2010-09-30 : 15:00:55

quote:
Originally posted by da42

Try charindex(Table1.Item,Table2.Items) > 0

I forgot to say I tried it, but didn't work either...

Sachin.Nand

2937 Posts

Posted - 2010-10-01 : 02:38:12

How about this?


SELECT table1.Name, table2.Benefits
FROM table1 INNER JOIN table2
ON (table1.Route = table2.Route
AND len(replace(Table2.Items,Table1.Item,''))<>len(Table2.Items))

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources