CODEWITHSUNDEEP
sql-server
hatched
hatched

Reputation: 865

MSSQL - How do I join table if the data type of field is different?

I'm joining few tables to display results

SELECT a.*, b.type 
    FROM [testing].[PICKDETAIL] a
    left join [testing].[ORDERS] b on a.ORDERKEY = b.ORDERKEY
    left join [testing].[LOC] c on A.LOC = c.LOC
    left join [testing].[CODELKUP] d on c.ZCOORD = d.CODE
    WHERE a.STATUS = 0 
    AND b.[TYPE] = 'RT-B'

But c.ZCOORD's data type is INT and d.CODE's data is NVARCHAR. Is there any way for me to join the table with this two fields?

Upvotes: 0

Views: 180

Answers (4)

Thom A
Thom A

Reputation: 95588

After looking at that query some more, I just had to post an answer and explain some of the problems with it:

SELECT a.*, b.type 
    FROM [testing].[PICKDETAIL] a
    left join [testing].[ORDERS] b on a.ORDERKEY = b.ORDERKEY
    left join [testing].[LOC] c on A.LOC = c.LOC
    left join [testing].[CODELKUP] d on c.ZCOORD = d.CODE
    WHERE a.STATUS = 0 
    AND b.[TYPE] = 'RT-B'

OK, where to start. Firstly, one of my biggest pet peeve's, your aliasing. Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3). The way you have it is very confusing. "d" is for CODELKUP and "c" is for LOC? How does that make any sense.

Next, your LEFT JOIN to ORDERS. You have a WHERE clause AND b.[TYPE] = 'RT-B'. This implicitly turns your LEFT JOIN to an INNER JOIN. This'll likely be bad for performance.

Also, c and d (what tables were they again?) are never referenced again after the ON clauses. They aren't in the WHERE or in the SELECT; so why are they there? They don't appear to be adding anything to the query.

Now, onto your question at hand, CODE is a nvarchar and ZCOORD is an int. If you were do leave it as is, then CODE would be implicity converted due an int, as per Data Type Precedence. This will be a problem if you have any values in CODE that can't be converted to an int; for example '1A01'. It'll also mean that values like '0101', '101' and '00101' would be treated as the same value (101); this may or may not be intended behaviour.

Either way, the implicit conversion will make your query non-SARGable, a nvarchar has a very different order structure to an int. For example with an nvarchar the follow expression would be true: '9' > '8465537632'. yes, that's right the "number" nine has a higher value than the "number" 8.4 billion.

The right solution here is to fix your data type, however, I don't know enough about your data to give the "correct" answer; thus I'm going to take the safer option of changing ZCOORD to an nvarchar, rather than code to an int.

Firstly, add the new column:

ALTER TABLE testing.LOC ADD COLUMN ZCOORDn nvarchar(100); --I don't know the data size, so you'll need to change this.

Next, you need to update the new column's value:

UPDATE testing.LOC
SET ZCOORDn = ZCOORD;

Now, we can drop your old column and add the new one:

ALTER TABLE testing.LOC DROP COLUMN ZCOORD;
GO
EXEC sp_rename N'testing.LOC.ZCOORDn',N'ZCOORD',N'COLUMN';

Now, you can write your query without concern of implicit conversion:

SELECT PD.*, --* is probably a poor choice. Declare your columns
       O.[TYPE] --Be consistance. This was [TYPE] in your WHERE, but it's type here.
FROM [testing].[PICKDETAIL] PD --Changed Alias
     JOIN [testing].[ORDERS] O ON PD.ORDERKEY = O.ORDERKEY --Changed Alias, also now an INNER JOIN
     LEFT JOIN [testing].[LOC] L ON PD.LOC = L.LOC --Changed Alias, is this needed though, it's not in the SELECT, so why is it here?
     LEFT JOIN [testing].[CODELKUP] C on L.ZCOORD = C.CODE --Changed Alias, is this needed though, it's not in the SELECT, so why is it here?
WHERE PD.[STATUS] = 0 
  AND O.[TYPE] = 'RT-B';

Note the comments I've put in the query above, there are quite a few.

Upvotes: 4

EzLo
EzLo

Reputation: 14189

If you are sure that all values of d.CODE will hold integers, then you can do an explicit cast. If this is the case, please consider changing the column data type to int as it will reduce size and boost performance.

SELECT a.*, b.type 
    FROM [testing].[PICKDETAIL] a
    left join [testing].[ORDERS] b on a.ORDERKEY = b.ORDERKEY
    left join [testing].[LOC] c on A.LOC = c.LOC
    left join [testing].[CODELKUP] d on c.ZCOORD = CONVERT(INT, d.CODE)
    WHERE a.STATUS = 0 
    AND b.[TYPE] = 'RT-B'

If you are not sure or it might hold non-integer values then your choice would be the other way around, converting c.ZCOORD to nvarchar.

SELECT a.*, b.type 
    FROM [testing].[PICKDETAIL] a
    left join [testing].[ORDERS] b on a.ORDERKEY = b.ORDERKEY
    left join [testing].[LOC] c on A.LOC = c.LOC
    left join [testing].[CODELKUP] d on CONVERT(NVARCHAR(20), c.ZCOORD) = d.CODE
    WHERE a.STATUS = 0 
    AND b.[TYPE] = 'RT-B'

Neither of these approaches are good for performance, as you are applying functions (converting data types) to the columns, which will invalidate the use of indexes that might exists on these columns.

If you leave it as it is, the SQL engine will most likely convert the most complex type to the simpler one, in this case it will do an implicit cast from NVARCHAR to INT, as comparing ints is way faster than nvarchars.

Upvotes: 1

Subhendu Bharti
Subhendu Bharti

Reputation: 56

You can try to cast d.Code to int datatype. use

left join [testing].[CODELKUP] d on c.ZCOORD = cast (d.CODE as int)

Upvotes: 1

Emdad
Emdad

Reputation: 832

You can try the following query 

SELECT a.*, b.type 
    FROM [testing].[PICKDETAIL] a
    left join [testing].[ORDERS] b on a.ORDERKEY = b.ORDERKEY
    left join [testing].[LOC] c on A.LOC = c.LOC
    left join [testing].[CODELKUP] d on c.ZCOORD = CAST(d.CODE AS INT)
    WHERE a.STATUS = 0 
    AND b.[TYPE] = 'RT-B'

Upvotes: 2

Related Questions