CODEWITHSUNDEEP
phpmysqldatabase
Tim Jahn
Tim Jahn

Reputation: 1174

How do I pull large number of multiple rows from one table?

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

Answers (2)

user557846
user557846

Reputation: 

select user_id 
from table 
where skill_id IN (10,11,12...)

make suer skill_is is indexed

Upvotes: 0

Jason Heo
Jason Heo

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

Related Questions