Prev: CASE Statement when ranges involved
Next: applications with Shared SQL users .. minimizing risk
From: Jack on 18 Jul 2008 10:31
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.
From: Eric Russell on 18 Jul 2008 10:48
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.


"Jack" wrote:

> 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.
From: vinu on 18 Jul 2008 10:49
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

"Jack" <Jack(a)discussions.microsoft.com> wrote in message
news:D85EDFDA-F646-4AC4-A7C7-42F897BE35D7(a)microsoft.com...
> 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.


From: SQL Menace on 18 Jul 2008 10:49
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

On Jul 18, 10:31 am, Jack <J...(a)discussions.microsoft.com> wrote:
> 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.

From: Chris.Cheney on 18 Jul 2008 10:50
=?Utf-8?B?SmFjaw==?= <Jack(a)discussions.microsoft.com> wrote in
news:D85EDFDA-F646-4AC4-A7C7-42F897BE35D7(a)microsoft.com:

> 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.
>

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

should do what you want.
 |  Next  |  Last
Pages: 1 2
Prev: CASE Statement when ranges involved
Next: applications with Shared SQL users .. minimizing risk