April 24, 2008 at 6:10 am
hi,
i have a task which requires joining two tables amongst othere. the tables do not have a common field. i'll try to put this into perspective to help you understand it better.
Table A Table B
A_id B_id
A_mpression B_desc
category dpreg
.... .....
...... .....
...... .....
Basically, B_id is made up of A_id + 3 other characters, (A_id, being the first 3). i need to join these tables and don't have an idea how to join with a substring.
I tried using this statement
SELECT SUBSTRING (A.A_id, 1, 3) AS g
FROM A INNER JOIN
B ON A.g = B.B_id
it gives an error, saying you can't call methods on nchar.
the fields are of type nchar.
does anyone have an idea of how to get this done?
Cheers
------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]
April 24, 2008 at 6:33 am
I think that your problem is trying to use the results of the substring in the join rather than then substring itself:
Try this:
SELECT SUBSTRING (A.A_id, 1, 3) AS g
FROM A INNER JOIN
B ON SUBSTRING (A.A_id, 1, 3) = B.B_id
Jez
April 24, 2008 at 6:47 am
thanks Jez,
that worked perfectly.
Cheers
------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply