mysql: joining tables + finding records with an AND style query, rather than OR

Question

Note: Using MySQL 4.0, which means no subqueries (at present).

I have 2 tables:

• A "user_details" table
• A "skills" table, which has the user_id and a "skill_id", which maps to a predefined set of skills defined elsewhere.

The current query allows an admin to search for users by selecting skills, and the query works in an OR fashion, eg:

LEFT JOIN skills 
ON (ud.user_id = skills.user_id)  
WHERE skills.skill_id in (51, 52, 53, 54, 55)
GROUP BY ud.user_id

This returns too many records and thus I want this search field to work in an AND fashion, where a user must have ALL the selected skills to be returned in the search.

It may be possible to get MySQL upgraded if subqueries are the best option.

edit: Something to do with group by, count, having etc. Can you restrict a group by command with a requirement on how many matched rows you return? (eg 5 in this example).

edit2: Testing out:

HAVING COUNT( * ) > 5

Answer 1

Just add more single joins.

INNER JOIN skills s ON u.id - us.userid AND skill_id = $s1
INNER JOIN skills s ON u.id - us.userid AND skill_id = $s2
INNER JOIN skills s ON u.id - us.userid AND skill_id = $s3
INNER JOIN skills s ON u.id - us.userid AND skill_id = $s4
etc.

It will be required to join to them all. You don't need any groups or counts.

Answer 2

You don't need a subquery or a join.

SELECT user_id
FROM skills
WHERE skill_id IN (51, 52, 53, 54, 55)
GROUP BY user_id
HAVING COUNT(*) = 5;

Answer 3

If you wanted the user details included in the same query you could simply do the following:

SELECT * FROM user_details  
JOIN skills USING (user_id) 
WHERE skill_id IN (51, 52, 53, 54, 55) 
GROUP BY user_id 
HAVING COUNT(*) = 5