Geek Answers Handbook

Mysql: joining tables + finding records with AND style query, not OR

Note. Using MySQL 4.0, which means no subqueries (currently).

I have 2 tables:

  • Table "user_details"
  • A skills table in which there is a user_id and skill_id, which maps to a predefined set of skills defined elsewhere.

The current request allows the administrator to search for users by selecting skills, and the request works using OR, for example:

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 therefore I want this search field to work in the AND module, where the user must have ALL selected skills that will be returned in the search.

You might be able to upgrade MySQL if subqueries are the best option.

edit: Do something with the group by, count, have, etc. Can you limit a team group to the requirement of how many matching lines you return? (e.g. 5 in this example).

edit2: Testing:

 HAVING COUNT( * ) > 5
+3
source share
3 answers

You do not need a subquery or connection.

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

Just add another join.

INTERNAL RELATED 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
and etc.

They will be required to join. You do not need any groups or accounts.

+1
source

If you want user data to be 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
0
source

More articles:


All Articles