Search code examples
mysqlsubqueryrowssql-like

MYSQL: LIKE (subquery) returning many rows

The following code work's properly.

SELECT Message
FROM SystemEventsR
WHERE Message
LIKE CONCAT('%',(SELECT username FROM users LIMIT 1),'%')

My question though, is most of the time i need to return more than one row.

I omit the LIMIT which results to an 'Error Code 1242'

Why is this?

I need all messages which contain at least one username

I also used a JOIN but to no avail

SELECT Message
FROM SystemEventsR
JOIN users ON Message LIKE CONCAT('%',(SELECT username FROM users),'%')

I would like something like the following, but the number of rows in users table is not constant

SELECT Message
FROM SystemEventsR
WHERE 
Message LIKE CONCAT('%',(SELECT username FROM users LIMIT 1,1),'%') OR
Message LIKE CONCAT('%',(SELECT username FROM users LIMIT 2,1),'%') OR
Message LIKE CONCAT('%',(SELECT username FROM users LIMIT 3,1),'%')
Solution

  • Why not simply;

    SELECT Message 
FROM SystemEventsR s
JOIN users u
  ON s.Message LIKE CONCAT('%',u.username,'%')

    An SQLfiddle to test with.