Using 'LIKE' with the result of a SQL subquery

Question

The following query is working absolutely fine for me:

SELECT * From Customers
WHERE Customers.ContactName = (SELECT FirstName
                               FROM Employees as E, orders as O
                               WHERE <condition>
                               LIMIT 1);

However, if i use LIKE instead of = to compare with the result of the subquery, I'm not getting any results. How do I use LIKE '%%' in the above query?

Answer 1

First, this query should not be working fine:

SELECT *
From Customers
WHERE Customers.ContactName = (SELECT FirstName
                               from Employees as E, orders as O
                               WHERE LIMIT 1);

Because WHERE LIMIT 1 is not proper SQL. And, you should learn to use proper join syntax. Presumably, you intend:

SELECT c.*
From Customers c
WHERE c.ContactName = (SELECT FirstName
                       FROM Employees as E JOIN
                            Orders as O
                            ON . . .
                       LIMIT 1
                      );

You could conceivably add LIKE instead of = and '%' in the subquery:

WHERE c.ContactName LIKE (SELECT CONCAT('%', FirstName, '%') . . .

But I would write this using EXISTS:

SELECT c.*
From Customers c
WHERE EXISTS (SELECT 1
              FROM Employees as E JOIN
                   Orders as O
                   ON . . .
              WHERE c.ContactName LIKE CONCAT('%', FirstName, '%')
             );

This does not do exactly the same thing as your query. It does something more reasonable. Instead of comparing one random name from the subquery, it will determine if there are any matches in the subquery. That seems a more reasonable intention for the query.