CODEWITHSUNDEEP
mysql
saadlulu
saadlulu

Reputation: 1395

using LEFT JOIN and LIKE mysql

i have a problem in mysql query. this is how my tables looks like:

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 do the following query, it works only if the specialty equals exactly the tags. but i want it 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 |
+------+

Upvotes: 4

Views: 14622

Answers (3)

Keeper
Keeper

Reputation: 3566

You're doing like wrong.

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: as per comments there's another way which should be better

Using REGEXP:

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

Upvotes: 9

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

Well, you've discovered the pain of saving independent values as a comma-delimited string.

If you can, I would suggest that you change your data structure, get the specialty column out of the user table, and create a new user_specialty table that would have foreign keys to users.username and tasks.tags.

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

Upvotes: 6

awm
awm

Reputation: 6570

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

FIND_IN_SET(task.tags,users.specialty)

(Reference)

Upvotes: 2

Related Questions