CODEWITHSUNDEEP
mysqlpattern-matchingsql-likeconcatenation
user965526
user965526

Reputation: 89

Comparing two tables with a LIKE and CONCAT %

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

Upvotes: 1

Views: 3361

Answers (1)

Ike Walker
Ike Walker

Reputation: 65567

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`, '%')

Upvotes: 1

Related Questions