Join

  • Hi

    I have two tables

    Table 1

    Price[Char(11)] TYPE[Char(1)]

    10.333 S

    10.3 B

    10.33 B

    Table 2

    Price[Char(11)] TYPE[Char(1)]

    10.3330 S

    10.30 B

    10.333 B

    I have to make a join on price column.If the there zero after the decimal point, should not be considered.

    eg. 10.333 and 10.3330 are same

    The output should be

    Price TYPE

    10.333 S

    10.3 B

    how to proceed on this

  • I would suggest that you cast both columns to numeric before joining. That way trailing zeros will be completely ignored. This, of course, assumes that all values in the column be castable to numeric.

    Why does your expected result have only 1 row with 10.333? If you're just joining on the price (as you said) then there should be 1 row with 10.333 S and one row with 10.333 B. Or do you want to join on both columns?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Not sure that I understand the way that you want the join, because the description that you wrote doesn’t match the results that you showed (or at least it didn’t match what I understood that you want). One way of doing it, is to base the join on convert function on both columns to money datatype:

    select t1.price, t1.type, t2.type

    from Table1 t1 inner join Table2 t2

    on convert(money, t1.Price) = convert(money,t2.Price)

    Notice that the performance won’t be good for such query. By the way – why are you storing price as string and not use a numeric data type?

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • You can even pad all the "0" till yuo reach length of the string as 11.

    http://www.developerdotstar.com/community/node/319

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply