Using LEFT JOIN and LIKE mysql

Question

I have a problem in mysql query. This is how my tables look:

mysql> select username, specialty from users; +----------+------------------+ | username | specialty | +----------+------------------+ | JinkX | php, html, mysql | | test1 | html | +----------+------------------+ mysql> select name, tags from tasks; +----------------+------+ | name | tags | +----------------+------+ | fix front page | html | +----------------+------+ 

and when I try to execute the following query, it only works if the specialty is exactly equal to the tags. but I want him to work on both

 mysql> select tasks.name from users left join tasks on tasks.tags LIKE users.specialty where users.username = 'test1'; +----------------+ | name | +----------------+ | fix front page | +----------------+ mysql> select tasks.name from users left join tasks on tasks.tags LIKE users.specialty where users.username = 'JinkX'; +------+ | name | +------+ | NULL | +------+ 

Answer 1

Well, you found pain when you save independent values ​​as a comma-delimited string.

If you can, I would suggest that you change the data structure, get the specialty column from the user table and create a new user_specialty table that has foreign keys for users.username and tasks.tags .

 +----------+------------------+ | username | tag | +----------+------------------+ | JinkX | php | | JinkX | html | | JinkX | mysql | | test1 | html | +----------+------------------+ 

Answer 2

You made a mistake like .

Try this query:

 select tasks.name from users left join tasks on users.specialty LIKE CONCAT('%',tasks.tags,'%') where users.username = 'JinkX' 

This is not the best way, but it should work

EDIT: according to the comments, another way that should be better

Using REGEXP:

 select tasks.name from users left join tasks on users.specialty REGEXP CONCAT('(^|,) ?',tasks.tags,' ?($|,)') where users.username = 'JinkX' 

Answer 3

You need the find_in_set function. Put this in the where clause:

 FIND_IN_SET(task.tags,users.specialty) 

(Link)