November 16, 2007 at 5:53 am
I am trying to compare two ID fields across linked DB's. Unfortunately, the data is not ideal (don't ask) and so the two tables have the following (example) data:
T1 T2
1 1
2 2
3 C3
4 C4
5 C5 ...
Leaving out collation, selects, joins, and where details, I've been using something like the following:
SELECT *
FROM LINK_T1 T1 LEFT OUTER JOIN T2 ON
CAST(T1.id AS varchar) = SUBSTRING(T2.id, 2, 9) -- i.e. left 'trims' the C
WHERE ...
This has worked OK for the more recent data (e.g. where 5 = C5), until I was asked to report across the history as well, where 1 = 1 and the above comparison gives 1 = Null. Now I need to compare for both options.
I'm fairly new to this and the things I've read / tried have got me to the above - is there a more elegant way to compare these fields?
Thanks
November 16, 2007 at 6:13 am
I'm sure there's a better way, but this will get you what you're looking for ....
SELECT *
FROM LINK_T1 T1 LEFT OUTER JOIN T2 ON
CAST(T1.id AS varchar) = SUBSTRING(T2.id, 2, 9) -- i.e. left 'trims' the C
OR
T1.id = T2.id
WHERE ...
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 16, 2007 at 6:32 am
Um ... thanks Jason
I figured this might turn out to be a 'duh!' moment for me ...
Many thanks for pointing out the obvious, logical option.
Andrew
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply