Joins

[Guest]

Hello,

I have two tables that I join using a unique eleven digit key.

Table A has the following attributes: name, address, city, state zip and
ext_zip. The ext_zip attribiute will always have 11 digits. Table B has the
same attributes as Table A in addition to others.

When I write a join statement, I joined Table A with Table B based on the
ext_zip attribute. This works great. The ext_zip attribute in Table A will
always have 11 digits while the ext_zip attriibute in Table B can have up to
11 digits.

I want to write a SQL statement that joins Table B and Table A on the
ext_zip attribute when Table B's ext_zip is less than 11 digits. Is there a
way to interate through the ext_zip attribute to see if, based on the number
of digits, there is a match to the ext_zip attribute within Table A?

Here is an example: Table B has ext_zip = 101160555 and Table A has ext_zip
= 10116055599. Instead of doing a like statement for each record within Table
B, is there a way to feed an SQL statement the ext_zip records from Table B?

I hope I explained this alright.

Thanks,
Mike

 

[Ken Snell \(MVP\)]

You use a non-equi-join query to do what you seek -- but note, such a query
will not allow you to edit data records returned by the query:

SELECT TableA.*, TableB.*
FROM TableA INNER JOIN TableB
ON TableA.ext_zip Like TableB.ext_zip & "*";