How to link 2 tables using substring values or alias fields?

noorkh · New User Joined: 06 Mar 2006 Posts: 76 Location: Chennai

Hi,

here is my requirement:

Table 1: Values are as explained below:
Descr Post_code
Name1 NR1 2DA
Name2 E6 2EA
Name3 NR1 1XY

Table 2: Values are as explained below:
Post code
NR1 2DA

1. I need to make search from TABLE2 and need to trim value into length of 3 but it would always return 1 row as it is mentioned above.
2. I need to make search from Table1 and need to trim value into length of 3 but it would be always more than 1.
3. I need to use trimmed value from 1 and 2 to fetch single matching record and show output of Postcode and descrip from Table1.

Can i do this in single query?

I want output to be displayed as
NR1 2DA Name1.

Akatsukami · Posted: Mon Sep 27, 2010 8:51 pm

noorkh · New User Joined: 06 Mar 2006 Posts: 76 Location: Chennai

How would you link 2 tables using their substring values( substring values derived after going through CASE condition)?

dbzTHEdinosauer · Posted: Tue Sep 28, 2010 4:33 pm

Noor,

why don't you show us what you have developed so far.

you keep mentioning 'trim to length of 3',
yet the column displays 7 char of data,
and to achieve the match (join) that you are describing, you need to use 7.

and why the need for the CASE?

why not select post_code, desc from table-1 where post_code exists (select post_code from table 2 where table-1.post_code = table-2.post_code)

pawaria · New User Joined: 06 Apr 2006 Posts: 7 Location: Noida

And if want to have post code trimmed to length of 3 in both tables

Select substr(post_code,1,3), Desc from table-1 where post_code exists (select post_code from table-2 where substr(table-1.post_code,1,3) = substr(table-2.post_code,1,3))

Guys pls let me know if made any mistakes in above query

Thanks
Pawaria