MYSQL comparison on trimmed and concatenated fields!

Penkowsky
Penkowsky used Ask the Experts™
on
I am stuck on this mysql query issue involving trimming, concatenation, and field comparison.

I have 2 tables.
- In the 1st table, I have a field that needs to be trimmed based on the character '-'
- In the 2nd table, I have 3 fields that need to be concatenated.
- Finally, I need to *match* LIKE fields from both tables together.

Example data below

ResultTable.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Select *
From  Table1 t1 INNER JOIN Table2 t2
           ON LEFT(t1.Field1,LENGTH(t1.Field1)-2) = CONCAT(CONCAT(t2.Field1,':',t2.Field2),':',t2.Field3)

Author

Commented:
The trim has to be based on the character '-'. As you can see, there are some fields in table 1 (eg. row 5) that do not have that '-' character, as well as some fields (eg. row 3) that are more than 2 characters long.
Commented:
Okay, let's try it this way:
Select *
From  Table1 t1 INNER JOIN Table2 t2
      ON SUBSTRING_INDEX(t1.Field1,'-',1) = CONCAT(CONCAT(t2.Field1,':',t2.Field2),':',t2.Field3)

Open in new window

Author

Commented:
Great, that works well with the simple modification of a single concatenation.

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