match full field from 1st table to first 9 characters in second table

[gjcomom]

I have been researching this one and I'm just not sure that I'm finding what I need or that I necessarily know what to call it. I'm thinking it is sort of a partial match but the things I'm trying are not returning anything.

I have an account numbers in two separate tables. I want to match the 9 digit acct number in the first table to the first 9 characters in the second table.

table 1.
123456789
456789123
789123456

table 2
123456789001
123456789002
123456789003
456789123003
234567891002
789987789006
456789123008

so I would want to return all the records from Table 2 except the one that starts 234.... and the one that starts 789....

I keep thinking this has to be fairly straight forward but I'm missing something.
thanks

[gjcomom]

well the formatting didn't quite work

editing previous post

[jforbes]

You'll probably want to use the Like Operator

Maybe something like this:

SELECT * FROM Table2
INNER JOIN Table1
ON Table2.AcctNumber LIKE Table1.AcctNumber & '*'

This uses an Inner Join, to return the Table2 record only if a record exists on both sides of the join.

[Narender Sagar]

You can also use =left([Field Name],9) function in a query and then in next query match this field with the Field in table 1.

[MikeTheBike]

Hi

Alternatively you could try

SELECT * FROM Table1 INNER JOIN Table2 
ON Table1.AcctNumber = Left(Table2.AcctNumber,9)
WHERE Left(Table1.AcctNumber,3) <> '234'
 

??

MTB