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 |
|
Apples
Posting Yak Master
146 Posts |
 Posted - 2008-03-06 : 12:57:39
|
| Here's my two tables:tblClients--------------------------------------------------ClientID (varchar(3)) | ClientName (varchar(100))--------------------------------------------------001 | First002 | SecondtblProjects---------------------------------------------------ProjectID (varchar(6)) | ProjectName (varchar(100))---------------------------------------------------001001 | First Project1001002 | First Project2002001 | Second Project1002002 | Second Project2As you can see, the ClientID is the first 3 digits of the ProjectID, signifying that a certain project belongs to that client. I know it's a bad design, but I can't change the DB, I have to use it.The problem is that I need to join the two tables based on the ClientID, but I don't know how to just retrieve the first 3 numbers in the ProjectID.I need something like:tblClients INNER JOIN tblProjects ONtblClients.ClientID = tblProjects.First3DigitsOfTheProjectID |
|
|
tprupsis
Yak Posting Veteran
88 Posts |
Posted - 2008-03-06 : 13:04:08
|
| Have you tried substring?tblClients INNER JOIN tblProjects ON tblClients.ClientID = substring(tblProjects.ProjectID,1,3) |
 |
|
|
Apples
Posting Yak Master
146 Posts |
Posted - 2008-03-06 : 14:56:50
|
| That worked great, thank you. |
|
|
|
|
|
|
Retrieving individual characters