Trying to use LIKE statement on JOIN query

[mnavarro]

Hello Everyone

I'm trying to make a query that returns the name of the 'bundle owner' according to the bundle number.

On the main table i have the bundle number in this format '676Z703009-1' and on the table that corresponds to the bundle owners we have the bundle number abbreviated like this '703009' (this is only the 6 digits after the 'Z' on the main table)

I need to make a query that looks if there is correspondence between the two tables so the query gives me the name of the bundle owner

I'm trying to use a query like this

SELECT squawks.*, squawksbbo.bundleowner AS 'bbo'
FROM squawks LEFT JOIN squawksbbo
ON(squawks.bundashno LIKE %squawksbbo.bundle%)
ORDER BY sqid DESC

I was thinking the '%' wildcard would help me on this but seems like its not helping at all. I made a test changing one of the main table bundles to its short form (the 6 digits only) and changed the 'ON' condition of the query to
ON(squawks.bundashno = squawksbbo.bundle) and it actually gives me the result I’m looking for but only on the row that the sort bundle name is located.

Any ideas?? Thanks on advance!!

[pty]

Hi, Rather than using wildcards I think you should try using the trim function on the string.

However if you have a big table this will be slow (but not as slow as wildcards!).

Maybe it would be faster if your numbers after the Z were in a separate column?

[mnavarro]

Thanks a lot! it worked fine... now i'm improving my code!!

[Maro18]

Thanks a lot! it worked fine... now i'm improving my code!!

Coud You write your solution? Thank You. :)