Search code examples
mysqlsqlselectsubquerysql-like

SELECT in the WHERE LIKE clause

I have the following SQL query:

SELECT * FROM `test2`
WHERE text LIKE (SELECT concat(startswith,"%") from test1 ORDER BY RAND() LIMIT 1)

Essentially I want to pick one row from the test1 table, and then find all the records from the test2 table that start with the characters from the startswith column in test1.

But this query doesn't do that. Where am I going wrong?

Here the tables in questing

SELECT * FROM `test1`

startswith
==========
aaa 
bbb 
ccc 

SELECT * FROM `test2`

text
====
aaa3k123k12312p03edwqeq 
aaa12313fwefrwerw   
aaafwre3we4232  
bbb123123rwqe12e1
Solution

  • use subselect in join like this 

    select t2.* from test2 as t2 join (SELECT RAND() as rand, startswith from test1 ORDER BY rand LIMIT 1) as t1 where t2.txt like CONCAT(t1.startswith,"%");