locked
Comparing data from two different tables RRS feed

  • Question

  • Hi

    I have two tables and each has one column.

    tableOne                     tableTwo

    do not breath dust        do not breath dust/vapor

    avoid contact with eyes  avoid contact with skin or eyes

    There are many variable like tableOne. (different meanings, different sentences) which need to be match the data from tableTwo. If i used like statement i need to create many like statements since i have many rows in both table. Should i create FullText Index to match data or any other options?

    Thanks,

    Friday, July 15, 2011 11:45 AM

Answers

  • If intent is to compare two column(s) with exact matchness of string then I would do with something called Fuzzy Matches or Fuzzy Lookup

    In past, when I ran into similar situation as yours where I need to compare two columns (where one column contains typos -- user input)  I used SQLCLR (.net dll) based approach (discussed in MVP deep dive book - MVP - Tom Van Stiphout)

    it is called Simil, basically you pass both column values from different tables and it can compare how similar those values are however it cannot be used to compare meaning of the words (like dust ~~ vapor)

     



    • Marked as answer by ellivkcor Tuesday, July 19, 2011 6:22 PM
    Friday, July 15, 2011 12:48 PM
  • Hi ellivkcor,

    Maybe you can try to use sys.dm_fts_parser to match the data from two columns in different tables. Could you please pay attention to this article elaborated on this issue: SQL Server: how to compare two tables.

    Please let me know if you need more help.


    Best Regards,
    Stephanie Lv

    • Marked as answer by ellivkcor Tuesday, July 19, 2011 6:22 PM
    Tuesday, July 19, 2011 6:30 AM

All replies

  • If intent is to compare two column(s) with exact matchness of string then I would do with something called Fuzzy Matches or Fuzzy Lookup

    In past, when I ran into similar situation as yours where I need to compare two columns (where one column contains typos -- user input)  I used SQLCLR (.net dll) based approach (discussed in MVP deep dive book - MVP - Tom Van Stiphout)

    it is called Simil, basically you pass both column values from different tables and it can compare how similar those values are however it cannot be used to compare meaning of the words (like dust ~~ vapor)

     



    • Marked as answer by ellivkcor Tuesday, July 19, 2011 6:22 PM
    Friday, July 15, 2011 12:48 PM
  • Hi ellivkcor,

    Maybe you can try to use sys.dm_fts_parser to match the data from two columns in different tables. Could you please pay attention to this article elaborated on this issue: SQL Server: how to compare two tables.

    Please let me know if you need more help.


    Best Regards,
    Stephanie Lv

    • Marked as answer by ellivkcor Tuesday, July 19, 2011 6:22 PM
    Tuesday, July 19, 2011 6:30 AM
  • Hi

    Can you make the database relational by assigning primary/foreign keys etc (it is quite streight forward if the rows are in the same order in both tables)? If so you can do a multi table select statement

    e.g. where col1 is the primary/foreign key and col2 contains the data you want to place side by side:

     

    SELECT table1.col2, table2.col2

    FROM table1, table2

    WHERE table1.col1 = table2.col1

     

    Hope this helps

     

    Phil

    • Proposed as answer by Phil Tyler Tuesday, July 19, 2011 9:28 AM
    • Unproposed as answer by Phil Tyler Tuesday, July 19, 2011 9:28 AM
    • Proposed as answer by Phil Tyler Tuesday, July 19, 2011 9:45 AM
    Tuesday, July 19, 2011 9:28 AM