Complex search problem in mysql query

I have models(tables) in my database with table and fields name like

tags                 (id, name)
taggings             (id, tag_id, taggable_id, taggable_type, context)
employment_histories (id, user_id, grades, subjects, my_interests )
users                (id)

taggable_id is actually employment_histories_id and context can either be grade or subjects or my_interests

now I have array of tags e.g. g={"9th","10th"} and I want to get users, only whose tags are all matching to the above g array.

I've written the query below:

SELECT DISTINCT users.* FROM `users` 
LEFT OUTER JOIN `employment_histories` 
  ON `employment_histories`.`user_id` = `users`.`id` 
LEFT OUTER JOIN `taggings` 
  ON `employment_histories`.`id` = `taggings`.`taggable_id` 
  AND `taggings`.`taggable_type` = 'EmploymentHistory' 
LEFT OUTER JOIN `tags` ON taggings.context = 'subjects' 
WHERE tags.name='9th' OR tags.name='10th'

but it gives me those users too, which match any of the tags, however I want that it will return only that user who match all the two tags

Suppose that tags 9th and 10th have tag id 9 and 10 then what i want that it will only return the taggable_id(which is employmenthistories.id) who has common taggable_id for these two tag_id (that is 9 and 10) in taggings table

for example i have two user tariq and kamal and both of these users have 9th tag common but kamal dont have tag 10th so want query which if passed these two tags should return only tariq whose tags are all macthing these two tags but users like kamal which match any of the 开发者_如何转开发tags should be filtered too

From php chat room:

SELECT
users.* ,
count(*) AS count
FROM users
    LEFT JOIN employment_histories ON  users.id = employment_histories.user_id
    LEFT JOIN tagging ON tagging.taggable_id = employment_histories.id
    LEFT JOIN tags ON tags.id = tagging.tag_id
WHERE tags.name = "9th"
       OR tags.name = "10th"
GROUP BY users.id
HAVING count = 2

SELECT users.* FROM users 
INNER JOIN employment_histories 
  ON employment_histories.user_id = users.id 
INNER JOIN taggings 
  ON employment_histories.id = taggings.taggable_id 
  AND taggings.taggable_type = 'EmploymentHistory' 
  AND taggings.context = 'subjects'
INNER JOIN tags ON tags.id = taggings.tag_id
WHERE tags.name IN ('9th','10th')
GROUP BY users.id
HAVING COUNT(DISTINCT(tags.name)) = 2;

I have re-wrote the query.

Few changes:

• Joining Tags on tags.id = taggings.tag_id
• Remove OR from where clause, and use in, improves the performance.

   SELECT DISTINCT users.*, count(*) as totRow FROM `users` 
   LEFT OUTER JOIN `employment_histories`
   ON `employment_histories`.`user_id` =
   `users`.`id`  LEFT OUTER JOIN
   `taggings`    ON
   `employment_histories`.`id` =
   `taggings`.`taggable_id`    AND
   `taggings`.`taggable_type` =
  'EmploymentHistory'    AND
   `taggings`.`context` = 'subjects' 
   LEFT OUTER JOIN `tags` ON `tags`.`id` = `taggings`.`tag_id` 
  WHERE tags.name = '9th' or tags.name = '10th'
  GROUP BY `users`.`id`