help

[userid128223]

Table A  -- Has mix of 3 & 6 digit zip codes

code	City
ABC123	Name1
ABD321	Name2
ABE	Name3
ABC	Name4
ABD	Name5

Table B   -- has all 6 digit

Code	City
ABC123	Name1
ABD323	Name7
ABE456	Name3
ABC784	Name4
ABD223	Name5

Need Solution:

I need to do inner join between A & B where first all 6 digit are matched once match are found you filter them from second inner join where match will be performed on first 3 digit code. final results will be combination of both matches.

[pamaxeed]

So in this example the output is ABC123 ....after the two inn er joins....

[userid128223]

No. Because at 6 digit that will be the match, but another inner join match needs to be perfromed excluding the match. now at first 3 digit level.

[userid128223]

Results

code	City
ABC123	Name1
ABE	Name3
ABC	Name4
ABD	Name5

[pamaxeed]

See the sample attached.

It should work, the output is the one u indicated.

Cheers,

Patric

[userid128223]

Thanks Patric, i don't see any code in the script.

[pamaxeed · Accepted solution]

Sry here you are .

[userid128223]

can you please explain the code. thx

[pamaxeed]

It is not complicated:

1. First inner join on code and city
2. Second inner join on the first 3 characters of code and city excluding the codes i have got as output from the first inner join, see the WHERE NOT EXISTS part
3. After that i concatenate the 2 output tables in one result table, and drop the two other tables.

Hope it helped u. Plz mark the answer as correct if it covered ur requirements

[userid128223]

Thx.

What does 2 parameters do in Where Not Exist:

Where Not Exists(RefCode, Code);