How to add * to a field

[DavidZ]

Hi All!



I am trying to return all main and associated job accounts from one table that have a prefix in another. My SQL is as follows:

SELECT FFFCOMPILE.Customer_ID
FROM FFFCOMPILE.Customer_ID INNER JOIN MatchingTest ON FFFCOMPILE.Customer_ID LIKE (MatchingTest.Customer_ID & '*')

Unfortunately, I am only returning rows that are in MatchingTest (that are an exact match with MatchingTest.Customer_ID), but I also want to return rows that have MatchingTest.Customer_ID as a prefix.

Can anyone tell me why this isn't working?

[June7]

I think eliminate the JOIN clause, resulting in a Cartesian relationship then use WHERE clause.

SELECT FFFCOMPILE.Customer_ID FROM FFFCOMPILE.Customer_ID, MatchingTest WHERE FFFCOMPILE.Customer_ID LIKE MatchingTest.Customer_ID & '*'

[orange]

There seems to be something wrong with your Tablename in my view.
FFFCOMPILE.Customer_ID

[DavidZ]

Thanks for your response, June7!

Just tried this, but unfortunately am still only returning rows that are in MatchingTest. Should I be doing something differently with how I add the wildcard?

[DavidZ]

Hi orange,

You're right. I wrote that incorrectly in the post, but in the query it is FFFCOMPILE.

[June7]

Post example raw data and example desired output.

[DavidZ]

I know what went wrong.

Thanks, June7, for having me go back to the basic building blocks. MatchingTest.Customer_ID was not a prefix for associated job accounts, but it was split and "sandwiched" associated job accounts. However, MatchingTest.CustomerName was a prefix, so in the end this worked:

SELECT DISTINCT FFFCOMPILE.Customer_ID
FROM FFFCOMPILE INNER JOIN MatchingTest ON FFFCOMPILE.Customer_ID LIKE MatchingTest.CustomerNumber & '*'

This was my 2nd SQL query ever but I will be doing a lot more moving forward. I will definitely be in this forum to get #educated!