I have 2 tables resumes and category. Resume table stores only th开发者_如何学Ce category id and the category table stores the cat id and name. Resume table category field values has comma separated value for example 1, 4, 6, 9 . Now the problem is I have to search the keyword hotel management which has the category id 6 in category table. What is the query to do this? Any one please help.
SELECT * FROM resumes t1 LEFT JOIN resumes_category AS t2 ON t2.`id` = t1.`category`
WHERE (
t1.name LIKE '%test%'
OR t1.keywords LIKE '%test%'
OR t1.description LIKE '%test%'
)
Along with this query, I have to search the resume category keyword hotel but resume table has category id only.
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';
精彩评论