Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Join

คำตอบ Join

  • Monday, February 04, 2013 5:22 PM
     
     
    Sign In to Vote
    0
    I'm trying to do a left join on a claim number and claim line number in one table to a claim numer and a claimline number in another table but one table has 1 through 9 but the second table has 01, 02, 03 through 09 so they can't match because one has 1 and the other has 01.  What can I do to make that work?
     

All Replies

  • Monday, February 04, 2013 5:24 PM
    Moderator
     
     
    Sign In to Vote
    0

    Do a transform on the keys: CAST (yourkey as INT)

    For quick assistance post code & DDL.

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

     
  • Monday, February 04, 2013 5:24 PM
     
     
    Sign In to Vote
    0

    Cast 2nd table's column to int e.g

    Select *

    from table1 left join table2 on table.id=cast(table2.id as int)

    vt

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

     
  • Monday, February 04, 2013 5:25 PM
    Moderator
     
     
    Sign In to Vote
    0

    What are the types of the columns? Try

    select T1.*, T2.*

    from Table1 T1 LEFT JOIN Table2 T2 ON RIGHT('00'+cast(T1.ClaimNumber as varchar(2)),2) = T2.ClaimNumber

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Monday, February 04, 2013 5:28 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    You can convert the numbers beginning with O to an integer

    DECLARE @tbl1 TABLE(
col1	INT);
INSERT @tbl1
VALUES(1), (2), (3);
DECLARE @tbl2 TABLE(
col1	CHAR(2));
INSERT @tbl2
VALUES('01'), ('02'), ('03');
SELECT *
FROM @tbl1 t JOIN @tbl2 tt
ON t.col1 = CONVERT(INT, tt.col1);

    David Dye My Blog

     
  • Monday, February 04, 2013 7:01 PM
     
     Answered
    Sign In to Vote
    0

    Thank you every one for such quick responses.  I was able to figure it out but I love the suggestions every one made.  I had the syntax incorrect.

    select

    distinct a.CLAIM, a.line, a.error1, a.error2, a.error3, a.billed_amt

    from

    dbo.HCFAencounters a

    left

    join [VPHP].dbo.b_claim_line b on a.CLAIM = b.claim_num

    left

    join [VPHP].dbo.b_claim_line c on c.CLAIM_LINE = case a.line when '01' then '1'

    when '02' then '2'

    when '03' then '3'

    when '04' then '4'

    when '05' then '5'

    when '06' then '6'

    when '07' then '7'

    when '08' then '8'

    when '09' then '9'

    else a.line

    end

    where

    a.DT_ENCOUNTER = '2012/08/01' and a.ERROR_LEVEL = '8'

    group

    by a.CLAIM, a.LINE, a.ERROR1, a.ERROR2, a.error3, a.billed_amt

    order

    by a.LINE

     
  • Monday, February 04, 2013 7:10 PM
     
     
    Sign In to Vote
    0
    you're joining mismatched data types you're going to have to lose somewhere. Whether it should be on the left or right hand side of a join depends entirely on the data density, and what indices are actually on the tables involved(e.g. It really doesn't cost a whole lot if a.foo isn't indexed anyway).
     
  • Monday, February 04, 2013 7:23 PM
     
     
    Sign In to Vote
    0

    Why do you group when there are no aggregate functions (SUM, AVG, MIN, MAX, etc)?

    Why are you using joins when you only select data from HFCAencounters?

    Why are you joining the same table twice on different keys?

    What if a.line = '001' ?  you should use a CAST or CONVERT as suggested to join the integers.  In your code you do not change the data type from string to numeric.  I would expect the values to be numbers in CLAIM_LINE