SQL join query

[original PM]

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

 

[woohoo]

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?

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.

 

[BrilloPad]

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

 

[original PM]

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

 

[woohoo]

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));

 

[SeanT]

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

 

[WTFH]

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.

 

[WTFH]

... 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.)

 

[BigRed]

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.

 

[Cirrus]

FTFY

FTFY

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.