joining data types - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

joining data types

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-06 : 22:42:31

Can anyone show me how to join 2 different data types in a SQL query. For example, I want to join two tables.

Table 1:
- Field 1 = ID (Varchar,10)
- Field 2 = Balance

Table 2:
- Field 1 = SSN (Numeric, 9)

Field ID should be = to SSN, but the data type is different. PLease help me to join them.

Thanks,

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-06 : 23:59:06

You can use cast or convert function to convert data type.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-06-07 : 01:00:02

select
t1.id,t1.balance,t2.ssn
from
table1 t1
inner join
table2 t2 on ( cast(t2.ssn as varchar(8000)) = t1.id )

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-09 : 07:28:20

Thanks for the help. However, I added additional two column that I was not indicate in previous question: Last_name and First_Name, they were blank as a result for that two columns and SSN column. Please let me know what I need to do in order to show data for those 3 columns...
Here is the query:

select a.id, b.first_name, b.last_name, sum(a.balance) as TotalLiabilities, b.ssn
from dba.tb1 a
left join dba.tb2 b on (cast(b.ssn as varchar(8000))=a.id)
group by a.id, b.first_name, b.last_name, b.ssn

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 07:31:46

quote:
Originally posted by ntn104

Thanks for the help. However, I added additional two column that I was not indicate in previous question: Last_name and First_Name, they were blank as a result for that two column. Please let me know what i did wrong...Because I want to see who belong to that ID, so I can verify in the second table.

Here is the query:

select a.id, b.first_name, b.last_name, sum(a.balance) as TotalLiabilities, b.ssn
from dba.tb1 a
left join dba.tb2 b on (cast(b.ssn as varchar(8000))=a.id)group by a.id, b.first_name, b.last_name, b.ssn

Thanks,

Can i ask why are you casting ssn to varchar? Is id in a also varchar? didnt understand why its varchar. ALso reason you're getting blank for fields of b will be that it wont be having a matching record for id value of a table

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-09 : 07:43:15

quote:
Originally posted by visakh16

quote:
Originally posted by ntn104

Thanks for the help. However, I added additional two column that I was not indicate in previous question: Last_name and First_Name, they were blank as a result for that two column. Please let me know what i did wrong...Because I want to see who belong to that ID, so I can verify in the second table.

Here is the query:

select a.id, b.first_name, b.last_name, sum(a.balance) as TotalLiabilities, b.ssn
from dba.tb1 a
left join dba.tb2 b on (cast(b.ssn as varchar(8000))=a.id)group by a.id, b.first_name, b.last_name, b.ssn

Thanks,

Can i ask why are you casting ssn to varchar? Is id in a also varchar? didnt understand why its varchar. ALso reason you're getting blank for fields of b will be that it wont be having a matching record for id value of a table

I would like to join two tables: tb1 and tb2 in order to get all outstanding liabilities. The only field that can be join: a.ID = b.SSN. But data type for a.ID= Varchar,10, and datatype for b.SSn=Numeric, 9. I don't know what I get the blank record...I may wrong on cast function.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-06-09 : 08:47:36

quote:
Originally posted by ntn104

quote:
Originally posted by visakh16

quote:
Originally posted by ntn104

Thanks for the help. However, I added additional two column that I was not indicate in previous question: Last_name and First_Name, they were blank as a result for that two column. Please let me know what i did wrong...Because I want to see who belong to that ID, so I can verify in the second table.

Here is the query:

select a.id, b.first_name, b.last_name, sum(a.balance) as TotalLiabilities, b.ssn
from dba.tb1 a
left join dba.tb2 b on (cast(b.ssn as varchar(8000))=a.id)group by a.id, b.first_name, b.last_name, b.ssn

Thanks,

Can i ask why are you casting ssn to varchar? Is id in a also varchar? didnt understand why its varchar. ALso reason you're getting blank for fields of b will be that it wont be having a matching record for id value of a table

I would like to join two tables: tb1 and tb2 in order to get all outstanding liabilities. The only field that can be join: a.ID = b.SSN. But data type for a.ID= Varchar,10, and datatype for b.SSn=Numeric, 9. I don't know what I get the blank record...I may wrong on cast function.

There is no mistake in cast function. Unless using cast or convert function u can't join two tables based on id.
Please post the sample data for your tables

ntn104
Posting Yak Master

175 Posts

Posted - 2008-06-09 : 12:58:40

quote:
Originally posted by raky

quote:
Originally posted by ntn104

quote:
Originally posted by visakh16

quote:
Originally posted by ntn104

Thanks for the help. However, I added additional two column that I was not indicate in previous question: Last_name and First_Name, they were blank as a result for that two column. Please let me know what i did wrong...Because I want to see who belong to that ID, so I can verify in the second table.

Here is the query:

select a.id, b.first_name, b.last_name, sum(a.balance) as TotalLiabilities, b.ssn
from dba.tb1 a
left join dba.tb2 b on (cast(b.ssn as varchar(10))=a.id)group by a.id, b.first_name, b.last_name, b.ssn

Thanks,

Can i ask why are you casting ssn to varchar? Is id in a also varchar? didnt understand why its varchar. ALso reason you're getting blank for fields of b will be that it wont be having a matching record for id value of a table

I would like to join two tables: tb1 and tb2 in order to get all outstanding liabilities. The only field that can be join: a.ID = b.SSN. But data type for a.ID= Varchar,10, and datatype for b.SSn=Numeric, 9. I don't know what I get the blank record...I may wrong on cast function.

There is no mistake in cast function. Unless using cast or convert function u can't join two tables based on id.
Please post the sample data for your tables

The reason it does not show data for those column that brought from tb2 because I choose left join. So the query is working now with below correct code:

select a.id, b.first_name, b.last_name, sum(a.balance) as TotalLiabilities
from dba.tb1 a
inner join dba.tb2 b on (cast(b.ssn as varchar(10))=a.id)
group by a.id, b.first_name, b.last_name
having sum(a.balance)>0

Thanks for your feedback

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources