Mysql - Get records satisfying all values in an array

Question

I have a table with fields

id  job_id  skill_id
1    1         1
2    1         2
3    1         3
4    2         1
5    3         1
6    3         2 
7    3         3

and have an array of values (skill_ids) (1,2,3).

What is the best way to get all job_ids having all these skill_ids ( ie I want to get 1 and 3 as my result set ).

Thanks in Advance !

score 1 · Answer 1 · answered Jul 28 '15 at 12:25

1

Use group by and having. The SQL you want to construct can look like:

select job_id
from table t
where skill_id in (1, 2, 3)
group by job_id
having count(*) = 3;

answered Jul 28 '15 at 12:25

Gordon Linoff

"array" has no meaning in the context of MySQL. Hence, you have an application layer and can construct the appropriate query in that layer. This is the structure of the query. – Gordon Linoff Jul 28 '15 at 20:20

score 0 · Answer 2 · answered Jul 28 '15 at 12:24

0

You can use ALL

something like: SELECT job_id FROM table WHERE skill_id = ALL (1,2,3)

answered Jul 28 '15 at 12:24

MatejKr

2 Answers2