MYSQL: LIKE (subquery) returning many rows

Question

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

Are you trying to get all messages with a valid username in them? If so you want a join. — Tony Hopkinson, Jun 02 '13 at 11:51
yes, i need all messages which contain at least one username. With a join I face a hard time with the "ON" part. Can you please give an example? — Apostolos Traganis, Jun 02 '13 at 12:04
To join two tables, you need to have a common column which will be the condition of ON . That's why I want to know the previous question — sarwar026, Jun 02 '13 at 12:25
No, unfortunately, there isnt any common column between the two tables (SystemEventsR and users) — Apostolos Traganis, Jun 02 '13 at 12:25
I guess, it is not possible to join in that case where there is no foreign key between these two tables. — sarwar026, Jun 02 '13 at 14:12

score 5 · Accepted Answer · answered Jun 04 '13 at 14:19

5

Why not simply;

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

An SQLfiddle to test with.

answered Jun 04 '13 at 14:19

Joachim Isaksson

176,943
25
281
294

score 1 · Answer 2 · answered Jun 02 '13 at 15:01

1

Can you try this:

SELECT Message
FROM SystemEventsR
WHERE Message
RLIKE (SELECT GROUP_CONCAT(username SEPARATOR '|') FROM users)

Check the manual regarding RLIKE .

answered Jun 02 '13 at 15:01

georgepsarakis

1,927
3
20
24

MYSQL: LIKE (subquery) returning many rows

2 Answers2