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 |
+------+
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With