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.
| Author |
Topic |
|
major-d
Starting Member
2 Posts |
 Posted - 2011-01-21 : 04:26:54
|
| Hi all,I have been trying to join two tables on unsimilar columns, the column in table 1 has an ID field and the column in table 2 has the same ID fields with a prefix of "vm-" and "host-" (for the mean time I am only interested in joining on the [vm-]ID columns.ID in table 1 is an int and ID in table 2 is a nvarchar so I have casted ID in table 1.My example Table 1ID_____66016660176601866019Table 2ID_____vm-66016vm-66017vm-66018vm-66019My Statementselect Col1A.Col1B,Col1Cfrom table1 vmLEFT JOIN table2 vaON CAST(vm.ID AS nvarchar (15))LIKE 'vm-'+va.MO_IDThe values returned from the joined table are NULLs which would suggest my ON is not working correctly.Any help would be greatly appreciated as I have not had to join two tables where there wasnt a common feature before.Thanks, |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-01-21 : 04:59:11
|
ON 'vm-' + CAST(vm.ID AS nvarchar (15)) = va.MO_ID
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
major-d
Starting Member
2 Posts |
Posted - 2011-01-21 : 05:07:31
|
| ah - i was doing 'vm-'+ on the wrong side of the link...so in fact i was looking for vm-vm-[ID] hence no met conditionsGreat - thank you so much for you assistance |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-01-21 : 05:09:01
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
Join on unsimilar columns