Clever Geek Handbook

Using "LIKE" with the result of a SQL subquery

The following query works perfectly 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 =

to compare with the result of a subquery, I don't get any results. How to use LIKE '%%'

in the above query?

+3


source to share


8 answers


First, this query shouldn't work fine:

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

Because WHERE LIMIT 1

SQL is not proper. And you must learn to use the correct syntax join

. Presumably, you intend to:

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

You could add LIKE

instead of =

and '%' to the subquery:



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

But I would write it with 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, '%')
             );

It does not do the same as your request. He's doing something smarter. Instead of comparing one random name from the subquery, it will determine if there are any matches in the subquery. This seems like a more reasonable intent for the request.

+2


source


I think this should work for you, no?



SELECT * From Customers
WHERE Customers.ContactName LIKE '%' + (
        SELECT FirstName from Employees as E, orders as O
        WHERE <condition>
        LIMIT 1
    ) + '%';
+1


source


Using your approach, you will need to wrap the FirstName field with a "%" on each side to place wildcards in the values ​​you choose.

So something like

SELECT * From Customers 
 WHERE Customers.ContactName LIKE (SELECT '%' + FirstName + '%'
                                    from Employees as E
                                   inner join, orders as O on...
                                   WHERE <condition>
                                   LIMIT 1);

It seems to me that you better join your tables. Maybe it will be better

SELECT c.* 
  FROM Customers c
 INNER JOIN Employees e on c.ContactName like '%' + e.FirstName + '%'
 WHERE <condition>
+1


source


The next query is an MSSQL statement . Instead, LIKE

I used . Try to execute the corresponding function in MySQL (check the function) instead . CHARINSDEX

INSTR

CHARINDEX 

SELECT
    *
FROM
    Customers
WHERE
    CHARINDEX(( SELECT  TOP 1 FirstName
                FROM    Employees as E, orders as O
                WHERE   <condition>
                ), Customers.ContactName, 1
             ) > 0;

I'm not sure, but you can try the following in MySQL

.

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


source


Why not use a simple one INNER JOIN

:

SELECT Customers.*
FROM Customers
INNER JOIN Employees ON Customers.ContactName LIKE CONCAT('%', Employees.FirstName,'%')
WHERE Employees.Foo = 'Bar'

Note: This +

is an addition operator and cannot be used for string concatenation.

+1


source


SQL Server

This will return a specific keyword

select * 
from Customers
where Customers.ContactName like (select top 1 FirstName from Employees )

This will return if the keyword exists

select * 
from Customers
where Customers.ContactName like '%' + (select top 1 FirstName from Employees) + '%'
0


source


I would suggest:

SELECT * From Customers as a
INNER JOIN (SELECT '%'+FirstName+'%' as FirstName from Employees as E, orders as O
                               WHERE <condition>
                               LIMIT 1) as b
ON a.ContactName LIKE b.Firstname;
0


source


If you have a foreign key to the employee table, you don't need to use LIKE . However, if you want to use it, you can try doing

select * 
from customers a, employees b 
where (contions on employees) 
  and customers.contactname like '%'||b.firstname||'%';
0


source






More articles:


All Articles