• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL join query

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    SQL join query

    Trying to join two tables but even though the data in the relevant fields is the same one of the fields is an INT and one is a VARCHAR

    Is there any easy way to just tell it to do the join and stop worrying?

    using sql2012

    #2
    You could try using a cast, though in this example with or without cast worked for me. It's going to perform horribly though. I'm assuming this is a one off?

    Code:
    CREATE TABLE TestA ( Age INT );
    
    INSERT  INTO dbo.TestA
            ( Age )
    VALUES  ( 30  -- Age - int
              );
    INSERT  INTO dbo.TestA
            ( Age )
    VALUES  ( 40  -- Age - int
              );
    
    CREATE TABLE TestB ( Age VARCHAR(255), );
    
    INSERT  INTO dbo.TestB
            ( Age )
    VALUES  ( '30'  -- Age - varchar(255)
              );
    
    SELECT  *
    FROM    dbo.TestA
            INNER JOIN dbo.TestB ON TestB.Age = TestA.Age;
    
    SELECT  *
    FROM    dbo.TestA
            INNER JOIN dbo.TestB ON TestB.Age = CAST(TestA.Age AS VARCHAR(255));
    If it's not a one-off I guess you could "fix" the data types or add a computed column with an index.
    Last edited by woohoo; 25 April 2017, 13:17.

    Comment


      #3
      Personally I have always found sql server to be very good at int/varchar. Perhaps only issue is the have an invoice number supposed to be an integer in a varchar field. If you say InvoiceNumber <> 0 that can cause issues if non integer values occur : so you have to use '0'.

      Biggest issue I have found is collation. LInked survers with difference default collations. gggrrrrrrr

      Comment


        #4
        It is probably going to be as easy to just dump into excel and then do a lookup

        I was trying to be clever but that rarely works!

        Cheers guys

        Comment


          #5
          Originally posted by original PM View Post
          It is probably going to be as easy to just dump into excel and then do a lookup

          I was trying to be clever but that rarely works!

          Cheers guys
          ok but what's the issue with trying a cast, looks simple. I'm genuinely interested if this didn't work.

          INNER JOIN dbo.TestB ON TestB.Age = CAST(TestA.Age AS VARCHAR(255));

          Comment


            #6
            I'm just chuckling at the title (I'd expect cast to work though)

            Comment


              #7
              How many records are we talking about, and how billable is all our time to fix it for you?

              If it doesn't work in a few minutes, I'll dump to excel and do it there, then go back in the future and try to work it out.
              …Maybe we ain’t that young anymore

              Comment


                #8
                ... and sack the guy who created the tables that way.

                Also, given that it's MicroShaft, if CAST doesn't work on one side, try it on the other, and then try it on both.
                (Can you tell I've had issues like this before? Field "order" is an 8 digit integer. Field "Original order" is an 8 character alphanumeric. Field "Related order" is an 8 character alphanumeric.)
                …Maybe we ain’t that young anymore

                Comment


                  #9
                  The CAST is going to make it non SARGable, but if you think you can dump it all into excel and use VLOOKUP the tables are obviously small enough to do full scans on.

                  Comment


                    #10
                    FTFY

                    Originally posted by original PM View Post
                    It is probably going to be as easy to just dump into Access.
                    There are only two things that might indicate there is a God.

                    The first is your liver is six times as big as it needs to be.

                    The second is MS Access.
                    "Don't part with your illusions; when they are gone you may still exist, but you have ceased to live" Mark Twain

                    Comment

                    Working...
                    X