June 8, 2004 at 2:00 pm
I am joining two tables on USERID. One table always is equal to the USERID where as the other table sometimes have a '~' in the front of the USERID.
I need to be able to join the two based on two conditions:
mid(table1.userid,2,10) = Table2.userid
or
table1.userid = table2.userid
I know I can put this together in the WHERE clause, but I need to make this a join. I tested using OR in the join, but it kept looping (though it didn't error out).
Could someone please lend me some advice? I would appreciate it!
June 8, 2004 at 2:05 pm
Just use replace and get rid of ~:
Table1 inner join Table2
on Replace(Table1.userid, '~', '') = Table2.userid
June 8, 2004 at 2:23 pm
Thanks so much for the fast reply. I tried it and it worked!
June 8, 2004 at 2:56 pm
Sweet. Better living through TSQL.
June 9, 2004 at 8:29 am
Johnny - that's an interesting solution. However, it works for this particular case where the join can be made via the replace command. What about when you want to conditionally join on something much more obtuse (e.g., different numerical keys, etc...)? Any suggestions?
June 9, 2004 at 9:16 am
Paul, One way to use conditionals is to use a case statement to separate the info.
As an example I have mashed two tables together (named GarbageData) into a view like this
| Type | IDNUmber | ColorID |
| car | 145 | 123 |
| car | 141 | 103 |
| person | 101 | 100 |
| person | 102 | 101 |
The ColorID field actually refers to 2 different tables _eyecolor and _VehicleColor
I would use this query to get the textual values from the respective tables
select type, IDNUmber,
Case
when type= 'people' then eyecolor
when type='cars' then Vehiclecolor
end
as
textcolor
from GarbageData
left outer join _eyecolor on color=pk_eyecolor
left outer join _Vehiclecolor on color=pk_Vehiclecolor
I hope that helps
tal mcmahon
June 9, 2004 at 10:43 am
Excellent! Thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply