The below are my tables with some rows as example and the search query. I am not sure if this is a correct solution, please could you just take a look and tell me if I should do anything in another way. I believe this can be a good example also for somebody else.
users
±------±------------+
| userId | fullName |
±------±------------+
| 4 | Nick Piterson |
| 4 | Nick Piterson |
| 55 | Mark Piterson |
±------±-------------+
user_categories
±------±------------+
| userId | categoryId |
±------±------------+
| 4 | 3 |
| 4 | 2 |
±------±------------+
categories_list
±-----------±--------------+
| categoryId | english |
±-----------±--------------+
| 3 | marketing |
| 2 | marketing |
±-----------±--------------+
offering
±-------±-------+
| userId | offering |
±-------±-------+
| 4 | 3 |
| 4 | 2 |
±-------±-------+
user_tags
±-------±-------------------------------------------------+
| userId | tags |
±-------±-------------------------------------------------+
| 4 | marketing, consulting, freelancer |
| 4 | social marketing, article writing, business travelling |
±-------±-------------------------------------------------+
promotion_package
±-------±----------+
| userId | package |
±-------±----------+
| 4 | 3 |
| 4 | 2 |
±-------±----------+
SELECT DISTINCT users.userId, users.fullName
FROM users
INNER JOIN offering ON users.userId=offering.userId
INNER JOIN user_tags ON users.userId=user_tags.userId
(INNER JOIN user_categories ON users.userId=user_categories.userId
INNER JOIN user_categories ON user_categories.categoryId=categories_list.categoryId)
INNER JOIN promotion_package.userId=users.userId
WHERE MATCH(users.fullName, user_tags.userTags,categories_list.english) AGAINST (‘$searchTerm’ IN BOOLEAN MODE)
AND user_categories.id=‘$user_categories’
AND offering.offering=‘$offering’
ORDER BY promotion_package.package
Thanks a lot!
