Reputation: 1174
This is an expansion of my original question located here: How do I pull all rows from a table with one unique field and specific values for another field?
I have a table with two fields: user_id and skill_id.
I want to pull out all rows that have a skill_id of a certain number but I have a large number of skill_id's to search for (~30). I was using the self-join suggestion presented in the question linked above but with so many skills to look for, that query is proving extremely slow.
How can I look for a large number of skill_ids without bogging down the query?
EDIT:
Here's an example of what I'm looking for. Using the table below, I want to pull out all rows of users that have skill_id of 10 AND 11 AND 12, etc. (except I'd be looking for more like 30 skills at a time).
TABLE
user_id | skill_id
=====================
1 | 10
1 | 11
1 | 12
1 | 13
2 | 10
2 | 12
2 | 13
3 | 15
3 | 16
4 | 10
5 | 45
5 | 46
Upvotes: 0
Views: 61
Reputation:
select user_id
from table
where skill_id IN (10,11,12...)
make suer skill_is is indexed
Upvotes: 0
Reputation: 10246
If I understand your question well, below query might help you. Assuming (user_id, skill_id)
is UNIQUE or PK.
SELECT user_id
FROM tab
WHERE skill_id IN (30 entries)
GROUP BY user_id
HAVING SUM(skill_id IN (30 entries)) = 30;
You can test here. http://www.sqlfiddle.com/#!2/f73dfe/1/0
Upvotes: 1