Comparing two tables with a LIKE and CONCAT %

Question

I have two tables

USERS Contains user data

Mobilephone
447777744444
447777755555
7777755555
7777766666

MOBILEPHONES Contains mobile phone numbers

Telephone No
7777744444
7777733333
7777755555
7777766666

If I run the follow SQL it returns ONLY numbers that match exactly and does not perform the wildcard search.

SELECT MobilePhones.*, users.FirstName FROM MobilePhones
LEFT JOIN users
ON (users.MobilePhone= MobilePhones.`Telephone No`)
WHERE users.MobilePhone LIKE CONCAT('%', MobilePhones.`Telephone No`, '%')

I get returned

7777755555
7777766666

What I want is

7777755555
7777766666
447777755555
447777744444

Answer 1

I think you probably want to move your WHERE clause into the ON clause of the join, replacing the existing ON clause, which is doing the exact match:

SELECT MobilePhones.*, users.FirstName 
FROM MobilePhones
LEFT JOIN users ON users.MobilePhone LIKE CONCAT('%', MobilePhones.`Telephone No`, '%')