Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Posts
    1

    Unanswered: Match a full column to a part of another

    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


    Code:
    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.

    charindex(Table1.Item,Table2.Items) > 0 doesn't work either, and I don't know why.

    Any thought on how to do this?

    Thanks in advance!

    Victor
    Last edited by vgarzon; 09-30-10 at 16:02.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vgarzon View Post
    But the "LIKE" comparison with wildcards does not work when comparing two columns.
    sure it does

    try it like this --
    Code:
    ','+REPLACE(table2.Items,' ','')+',' LIKE '%,'+table1.Item+',%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I can not replicate the issue on SQL 2008;
    Code:
    create table test1
    (col1 int identity(1, 1),
     col2 char(2))
    
    create table test2 
    (col1 int identity(1, 1),
     col2 varchar(100))
    
    insert into test1 (col2) values  ('xx')
    
    insert into test2 (col2) values ('aa,bb,cc')
    insert into test2 (col2) values ('aa,xx,cc')
    insert into test2 (col2) values ('aa,bb,xx')
    
    select *
    from test1 a join
    	test2 b on b.col2 like '%' + a.col2 + '%'
    
    COL1        col2 col1        col2      
    ----------- ---- ----------- ----------
    1           xx   2           aa,xx,cc
    1           xx   3           aa,bb,xx
    
    (2 row(s) affected)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •