Joining two tables after manipulating a field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jack - 18 Jul 2008 15:31 GMT

Hi I have say table A and table B
Table A has the following data.

proj_num contact_name contact_phone
05-0001-00 Sam Eaton 111-1111
05-0002-00    Bem Frank 555-5555
06-0004-01    Judy Yamaguchi 888-8888

Table B hs the following data:
proj_num project_cost
05-0001     20,000.00
05-0002        40,ooo.00
06-0004        50,000.00

For some reason the project_num in table B is stored in a different way.
However 05=0001-00 in Table A corresponds to
05-0001 in table B. This pattern continues.

Now in order to find information between the two table a join is required.
However in the join one has to manipulate the Table A proj_num to strip off
the -xx(xx is value from 0 to 99) in the last portion. This will succeed the
join. How does one handle this join. Thanks in advance.

Reply to this Message

Eric Russell - 18 Jul 2008 15:48 GMT

You can join on the left 7 positions of the proj_num column.

select
...
from A
left join B
on B.proj_num = left(A.proj_num,7)

It looks like proj_num in table A contains something like a 2 digit
sub-project suffix. Confirm that with whomever owns table A.

> Hi I have say table A and table B
> Table A has the following data.
[quoted text clipped - 18 lines]
> the -xx(xx is value from 0 to 99) in the last portion. This will succeed the
> join. How does one handle this join. Thanks in advance.

Reply to this Message

vinu - 18 Jul 2008 15:49 GMT

Jack

try

select a.proj_num,a.contact_name,a.contact_phone,b.project_cost
from table_a A inner join table_b B on a. proj_num=left(a. proj_num,7)

vinu

> Hi I have say table A and table B
> Table A has the following data.
[quoted text clipped - 20 lines]
> the
> join. How does one handle this join. Thanks in advance.

Reply to this Message

SQL Menace - 18 Jul 2008 15:49 GMT

Does this work for you?

select * from tablea a
join tableb b on b.proj_num = left(a.proj_num,7)

Denis The SQL Menace
http://www.lessthandot.com/
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

Reply to this Message

Chris.Cheney - 18 Jul 2008 15:50 GMT

> Hi I have say table A and table B
> Table A has the following data.
[quoted text clipped - 19 lines]
> portion. This will succeed the join. How does one handle this join.
> Thanks in advance.

ON LEFT(A.proj_num, 7) = B.proj_num

should do what you want.

Reply to this Message

Jack - 18 Jul 2008 16:10 GMT

Thanks Eric, vinu, plamen, sql menace, and Chris for your help to resolve
this. I appreciate it very much. Regards.

> > Hi I have say table A and table B
> > Table A has the following data.
[quoted text clipped - 23 lines]
>
> should do what you want.

Reply to this Message

Plamen Ratchev - 18 Jul 2008 15:51 GMT

You can remove the extra portion in the JOIN condition:

SELECT <columns>
FROM TableA AS A
JOIN TableB AS B
ON LEFT(A.proj_num, 7) = B.proj_num;

For performance it will be better to use a calculated column that removes
the extra characters and have an index on the column. Alternatively an
indexed view will work too.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message