Can You Join Two Tables When the Data Types Do Not Match? 1

[lrdave36]

I am trying to join two tables which both share the social security number as a common field.

The problem is the fields are not the same datatype. In one table, it is varchar, and the other table has the field as integer.


Is there any way around this obstacle? Maybe a function that temporarily converts to a different data type?

My query looks like this:

SELECT   C.MBR_F_NM,
         C.MBR_L_NM,
         A.FOLDID,
         B.USERID,
         B.TIMECRTE,
         B.SHRTDESC,
         B.NOTETEXT
         
         
FROM     DSNP.EYPTFOLD01 A,
         DSNP.EYPTNOTE01 B,
         DSNP.PR01_T_MBR C
         
         
WHERE    A.CLUSTID=B.CLUSTID
AND      A.FOLDID=C.MBR_SSN_NBR
AND      B.USERID = 'XXXXX'

 

[markros]

Use cast or convert function to convert integer to character, e.g.

select ...

FROM .. A INNER JOIN ... B ON ...
INNER JOIN .. C ON
convert(varchar(10),A.FoldID) = C.MBR_SSN_NBR

---------
I also suggest a new style JOIN instead of the old style WHERE.

PluralSight Learning Library

 

[lrdave36]

Thanks Markros! I'm still having a hard time letting go of the old ways. lol The where clause is easier for me and I only use inner joins.

 

[gmmastros]

lrdave36,

It's true that both style of joins work equally well when you are using inner joins. I won't try to convince you to switch, but I would like to take this opportunity to explain why I prefer the new style join.

In my opinion, it makes the code easier to read and easier to understand. Look at it this way, with the old style join, you need to look at the where clause to see how the tables relate to each other. With the new style join, you look at the ON clause. Since the ON clause immediately follows the join condition, you can easily see how the tables are related. When you do things the new style, the where clause is usually reserved for filter criteria (and only filter criteria).

When I look at a query, this is how I think of it.

[blue]
SELECT   C.MBR_F_NM,
         C.MBR_L_NM,
         A.FOLDID,
         B.USERID,
         B.TIMECRTE,
         B.SHRTDESC,
         B.NOTETEXT
[/blue][green]         
FROM     DSNP.EYPTFOLD01 A
         INNER JOIN .EYPTNOTE01 B
           ON A.CLUSTID = B.CLUSTID
         INNER JOIN DSNP.PR01_T_MBR C
           ON Convert(VarChar(10),A.FOLDID) = C.MBR_SSN_NBR
[/green][red]         
WHERE    B.USERID = 'XXXXX'[/red]

1. If I want to know what data is returned from the query, I simply look at the SELECT clause.

2. If I want to know what tables are used and how they relate to each other, I look at the FROM clause.

3. If I want to know the filter criteria, I look at the WHERE clause.

In my opinion, this allows me to look at and understand the query a lot faster because the 3 different parts of the query are grouped together.

I noticed from your original query that you listed the join conditions first in the where clause and then added the filter criteria. So, essentially you have the FROM clause that shows the tables and the first part of the where clause shows how the tables are related and the last part of the where clause shows the filter criteria. I can certainly understand why this format is preferable to some people.

Basically the difference is: With the new style, the relationship between the tables is defined along with the list of tables. With the old style, you have all the tables and then the relationships.

Another reason I prefer the new style is because it helps me to prevent mistakes. With the old style, it is easier to miss a table relationship. Suppose you had 10 tables in your query, you would need to have 10 where clause criteria to define the table relationships, and it would be easy to miss one of them. With the new style, the ON clause follows each table, so it would be more obvious that you missed a condition.

Lastly, the large majority of sample code on the internet is written in the new style.

The simple truth is, both methods work exactly the same for inner joins. You will always get the same results, and the execution plans will always be the same too. Again, I'm not necessarily trying to convince you that you need to get comfortable with the new style. I am simply explaining my reasons.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lrdave36]

Thanks George. You make some good points here.


I can't thank you guys enough for all your help. I've learned a lot posting here on this forum.

 

[SkipVought]

This FAQ was written to address issues faced in Excel, but the principles are applicable...

faq68-6659

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]