tags:

views:

520

answers:

1

I have 2 tables resumes and category. Resume table stores only the 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.

+2  A: 

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';
Andrew Moore
Worth +1 each for both halves of the answer. Note that FIND_IN_SET can return 0 if id is not found in category and neither are null.
martin clayton
Thanks for helping. I have used FIND_IN_SET as you said and it worked out well. Thanks andrew
Krishna Priya
@Krishna Priya: If the answer above worked, please consider marking it as accepted (click the checkmark outline on the left of the answer).
Andrew Moore