Join query if no referential integrity bw two tables

kethepalli vagdevi · New User Joined: 07 Sep 2006 Posts: 2

There are two tables .
Table 1 contains 3 fields say A (primary key), B, C
Table 2 contains 2 fields say X,Y - no primary key.

We need to join these two tables.
There is no referential integrity between these tables.
Tha values in field A(char 3) are same as values in first 3 characters of field X(char 8).
and we need to join , table 1 and table 2 based on field A (of size 3) with only first 3 characters of field X(of size 8) .

There are more than 1 record for field A in table 2(field X).

eg : Field A has value 'MNP'
Field X has value 'MNP00045'

sarunkumaar · Posted: Thu Sep 07, 2006 1:37 pm

Use substring to match the keys.

SELECT COLUMNS_NEEDED FROM TABLE01 T1, TABLE02 T2
WHERE SUBSTR(T2.X,1,3) = T1.A