What you are describing is a Many-To-Many relationship.
In relational databases, you cannot express a many-to-many relationship using only the two tables involved. You must break up the many-to-many into 2 one-to-many relationships using what is called a link table.
Your tables then become the following:
+---------------------+ +---------------------+ +---------------------+
| resumes | | categories | | resumes_categories |
+---------------------+ +---------------------+ +---------------------+
| resume_id (PK) | | category_id (PK) | | resume_id (PK,FK) |
| other_fields | | name | | category_id (PK,FK) |
+---------------------+ | keyword | +---------------------+
| description |
+---------------------+
Using this table layout, your queries then become relatively easy:
SELECT resumes.*, categories.*
FROM resumes LEFT JOIN (resumes_categories, categories)
ON (resumes.resume_id = resumes_categories.resume_id AND
categories.category_id = resumes_categories.resume_id)
WHERE categories.name = 'test';
If you are stuck using the database layout you are currently using, MySQL has a function called FIND_IN_SET()
. It returns an integer representing the position of the found string, or NULL
.
Note that this function is slow and I would recommend changing your database layout.
SELECT *
FROM resumes, resumes_category
WHERE FIND_IN_SET(resumes_category.id, resumes.category) IS NOT NULL
AND FIND_IN_SET(resumes_category.id, resumes.category) > 0
AND resumes_category.name = 'test';