locked
comparing 2 tables RRS feed

  • Question

  • User-1809536021 posted

    Hi Friends, I need your help to solve this.

    I have 2 tables tableA and tableB.

    Both tables having hotelnames with different character lenght.

    I want list hotel id of Table a and Table b only when the hotelnames of the both tables r same. how to achive this. i dont have any relation between tables. how to compare 2 varchar columns

    help me please. 

    Friday, October 15, 2010 2:21 PM

Answers

  • User77042963 posted

    select * from resortcheck r join listhotelshotel l on ltrim(rtrim(l.hotelname))  like '%'+ltrim(rtrim(r.resortname))+'%'

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 15, 2010 4:28 PM

All replies

  • User-1360095595 posted

    I think you can simply do an INNER JOIN on the two tables on the hotelname column. 

    Friday, October 15, 2010 2:39 PM
  • User77042963 posted

    SELECT  a.*, b.* FROM tableA a INNER JOIN tableB b on  a.hotelnames=b.hotelnames

    Friday, October 15, 2010 2:40 PM
  • User-1809536021 posted

    Thanks a lot for your replys.

    But i dont have any relationship between 2 tables. 2 tables having different varchars of different columnnames. i compared using inner join but i did not get any result. but when i check manulay i found some matching records. 

    to create inner join do i need to have relation ship between tables. if not how to compare 2 character columns on inner join. 

    Friday, October 15, 2010 2:51 PM
  • User-1809536021 posted

    i am using query like this

    select * from tableA a join tableB b on a.hotelname like  '%b.hotelname%'

    but it is not working

    giving 0 records 

    Friday, October 15, 2010 2:59 PM
  • User77042963 posted

    You need to tell us about your columns' data type.  Are you sure both hotelnames are varchar type?

    Friday, October 15, 2010 3:05 PM
  • User77042963 posted

    You may need to use RTRIM/LTRIM on your columns to remove white spaces. 

    Friday, October 15, 2010 3:06 PM
  • User-1809536021 posted

    select * from resortcheck r join listhotelshotel l on ltrim(rtrim(l.hotelname)) like '%ltrim(rtrim(r.resortname))%'

    giving 0 results

     

    Friday, October 15, 2010 3:10 PM
  • User77042963 posted

    Can you provide your table structures and some sample data you can find the result manually? Thanks. 

    Friday, October 15, 2010 3:16 PM
  • User-1809536021 posted

    yes i can find results manually.

    here is the table structures

    listhotelshotel

    hotelid int

     hotelname nvarchar(150)

    resortcheck

    resortid  nvarchar(50)

    resortname nvarchar(100)

    1231            hotel6
    1233            Locanda Chiassaia
    1237            Annexe
    1234            Le Magnolie
    1230            Hotel Molnar
    1234            Manzard Panzio
    1238            Etna B&b

    49          hotel6

    199         Condominios Playa del Sol
    1900      Island Beach & Tennis
    2191       Montecristo Estates by Pueblo Bonito
    1934      Boutique Resort La Jolla
    1664         Silverleafs Seaside Resort
    257        Christmas Mountain Village

    thank a lot limno for your replies.

    Friday, October 15, 2010 3:21 PM
  • User77042963 posted

    Can you find a pair of data from these columns that you think they are same? Copy and paste them here. Do you think they may contain other hidden symbols ? Sometimes you may not able to see them but the computer will tell you that they not the same. You need a way to remove these symbols before you can compare them.

    Friday, October 15, 2010 3:26 PM
  • User-1809536021 posted

    1231            hotel6
    49          hotel6

    of both tables having same hotelnames . i need to show their ids.

    like the way i need to compare 100000 records of both tables. i dont think the names having special symbols

     

    Friday, October 15, 2010 3:38 PM
  • User77042963 posted

    select * from resortcheck r join listhotelshotel l on ltrim(rtrim(l.hotelname))  like '%'+ltrim(rtrim(r.resortname))+'%'

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 15, 2010 4:28 PM