0

I have this simple table (called jobs_criterias) :

+-----------+---------+-------------+
| id        | job_id  | criteria_id |
+-----------+---------+-------------+
|       101 | 4       | 3           |
|       105 | 6       | 5           |
|       130 | 8       | 5           |
|       132 | 9       | 5           |
|       133 | 6       | 7           |
|       150 | 7       | 8           |
|       160 | 8       | 9           |
+-----------+---------+-------------+

I want to find the job_id which have same criteria_id.

For example, I want to search job_id for criteria_id IN (5, 7). It should return 6 with the sample data (only job_id = 6 matches both criteria_ids)

After some searchs, I use this query :

SELECT DISTINCT 
   job_id
FROM jobs_criterias jc1
   INNER JOIN jobs_criterias jc2 USING(job_id)
WHERE jc1.criteria_id = 5 AND jc2.criteria_id = 7

It works but this query is not easily extensible. I use query builder from PHP framework and it's hard to build this kind of queries.

Is there any simpler way to achieve this ?

Vincent Decaux
  • 9,857
  • 6
  • 56
  • 84
  • 1
    Assuming there is a ```UNIQUE``` constraint on ```(job_id, criteria_id)``` (or the other way around), you should be able to remove the ```DISTINCT```. What exactly is your problem, what do you want to avoid? – Islingre Nov 04 '19 at 20:42

1 Answers1

2

You can use a grouping query to count the number of criteria_id which a given job_id matches, selecting only those that match all of them:

SELECT job_id
FROM jobs_criterias
WHERE criteria_id IN (5, 7)
GROUP BY job_id
HAVING COUNT(DISTINCT criteria_id) = 2

Output

6

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95