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

score 2 · Accepted Answer · answered Dec 12 '08 at 00:33

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;

answered Dec 12 '08 at 00:33

Bill Karwin

538,548
86
673
828

dkretz · Answer 2 · 2008-12-12T00:35:55.387

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.

edited Dec 12 '08 at 00:35

answered Dec 12 '08 at 00:27

dkretz

37,399
13
80
138

That doesn't exactly scale well. MySQL queries have a 31-join limit (or 63-join if you compile MySQL for 64bit hardware). And long before then, the performance of a massively joined query will be unacceptable. – Bill Karwin Dec 12 '08 at 00:31
How often do you want to find someone with 32 simultaneous skills? – dkretz Dec 12 '08 at 00:34
Every job interview I've had in recent years has demanded 32 simultaneous skills. ;-) – Bill Karwin Dec 12 '08 at 00:36
Then they deserve to slow to a crawl. :) (But the example was 5, which I think makes my solution faster.) – dkretz Dec 12 '08 at 00:38
That really only means I'd try both, but I'd try mine first. – dkretz Dec 12 '08 at 00:38
Nope, because then you'd get back all rows from the users table, because every outer join would return NULL. It *would* work if you then concatenated the skill_id's together for display, and then tested that for null - any missing concat string nulls the whole expression. – dkretz Dec 12 '08 at 01:14

score 0 · Answer 3 · answered Dec 12 '08 at 01:46

0

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

answered Dec 12 '08 at 01:46

djt

461
3
5

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

3 Answers3