Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Comparing column data in two tables

    DBA's of SQL Server:

    I have two tables, the columns of which I need to compare.

    Table A
    Col1...............Col2
    Name.........ID

    ABC.......... 1
    DEF .......... 2
    WXY...........3


    Table B

    Col1.......Col2.....Col3
    Name1...Name2..Name3
    A........... B.........C
    D.......... G..........Z

    I need to output every record of Table A where the even a single alphabet in name column matches with Table B.

    So as in the above example there is a match for record 1 and record 2( D in DEF matches with record2 Name1 in Table B) in Table A but record3 (WXY) does not match with any of the alphabets of Table B. So I should get only the first two rows of Table A. Can anyone help me structure this query?

    Appreciate your help.
    Last edited by vivek_vdc; 12-04-03 at 12:09.

  2. #2
    Join Date
    Oct 2002
    Location
    Argentina
    Posts
    72

    Re: Comparing column data in two tables

    Try something like this

    select * from tablea ta , tableb tb
    where
    ta.name like '%' + convert(varchar(1),tb.name1) +'%'
    or
    ta.name like '%' + convert(varchar(1),tb.name2) +'%'
    or
    ta.name like '%' + convert(varchar(1),tb.name3) +'%'


    Originally posted by vivek_vdc
    DBA's of SQL Server:

    I have two tables, the columns of which I need to compare.

    Table A
    Col1...............Col2
    Name.........ID

    ABC.......... 1
    DEF .......... 2
    WXY...........3


    Table B

    Col1.......Col2.....Col3
    Name1...Name2..Name3
    A........... B.........C
    D.......... G..........Z

    I need to output every record of Table A where the even a single alphabet in name column matches with Table B.

    So as in the above example there is a match for record 1 and record 2( D in DEF matches with record2 Name1 in Table B) in Table A but record3 (WXY) does not match with any of the alphabets of Table B. So I should get only the first two rows of Table A. Can anyone help me structure this query?

    Appreciate your help.

  3. #3
    Join Date
    Sep 2003
    Posts
    176

    Re: Comparing column data in two tables

    This doesn't work. Here is what happens. Let me make the tables simple.

    Table A
    Name........ID
    B C A..........1
    D E F...........2
    A...............3


    Table B

    Name1
    A

    I need all records that contain alphabet A. With the query -

    select * from tablea ta , tableb tb
    where
    ta.name like '%' + convert(varchar(1),tb.name1) +'%'

    Only record 3 (A....3) is returned. I also need record 1. Note that the individual alphabets are separated by spaces.

    Let me know. Thanks.


    Originally posted by fhunth
    Try something like this

    select * from tablea ta , tableb tb
    where
    ta.name like '%' + convert(varchar(1),tb.name1) +'%'
    or
    ta.name like '%' + convert(varchar(1),tb.name2) +'%'
    or
    ta.name like '%' + convert(varchar(1),tb.name3) +'%'

  4. #4
    Join Date
    Sep 2003
    Location
    Mumbai, India
    Posts
    36
    Here you go. I created two tables exactly as you specified:
    create table table1
    (Name varchar(3)
    ,ID int identity (1,1))
    It has values:
    ABC, 1
    DEF, 2
    WXY, 3

    create table table2
    (Name1 char(1)
    ,Name2 char(1)
    ,Name3 char(1))

    It has values:
    A, B, C
    D, G, Z

    Now run this:

    select distinct table1.Name, table1.ID from table1, table2
    where charindex (substring(NAME,1,1), Name1+Name2+Name3) <> 0
    or charindex (substring(NAME,1,2), Name1+Name2+Name3) <> 0
    or charindex (substring(NAME,1,3), Name1+Name2+Name3) <> 0

Posting Permissions

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