help with query

jamesmetcalf74
jamesmetcalf74 used Ask the Experts™
on
I need to do a join on two fields but only part of the data in the fields.
the field is 18 digits long but I need the join to be dependent on only the first ten digits.

example.
2 account id's below

000000000055555555
000000000066666666

I need them to match by the first ten digits so the two above referenced accounts would result in a match
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
ON LEFT(col1, 10) = LEFT(col2, 10)
Top Expert 2015
Commented:
Hi

SELECT  * FROM tablename1 tn1 INNER JOIN tablename2 tn2 ON (substring(tn2.yourcolname, 1,10) = substring(tn1.yourcolname, 1,10))
ste5anSenior Developer

Commented:
Review your model. Looks like a violation of 1NF.

Author

Commented:
Worked like a charm Scott.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial