Mysql - SQL: Filtering a column based on AND conditions for 2 other columns

HamzaAsaad
November 12, 2023
297 views
0 votes
2 Answers

I have an assessments table and a table which has its id and holds the criteria for its recipients. So assessment_id, criteria_id and criteria_type(designation, department, city and project). Each assessments can be based on multiple of each criteria so an assessment can have 2 designation IDs for example. Now in the future a new user has been created and they can fall into the criteria for this assessment how would I query this table to filter out the correct assessment_ids.

SELECT assessment_id
FROM Assessment_Criteria
WHERE (criteria_id, criteria_type) = (1, 'designation')
  AND (criteria_id, criteria_type) = (1, 'department');

This was my first attempt but it fails since the AND condition cannot be true here.

Tags: mysql

Answers

- AdrianMaxwell
- November 12, 2023 at 9:03 am
- 0 votes
0
It isn’t common to compare multiple columns together as you have done, but it is possible. The problem you face is that a single row cannot meet both rows of your where clause
```
WHERE (criteria_id, criteria_type) = (1, 'designation')
  AND (criteria_id, criteria_type) = (1, 'department')
```
i.e. you are asking for criteria type to be BOTH ‘designation’ & ‘department’ which isn’t possible.

So your existing where clause needs to use OR like this:
```
WHERE (criteria_id, criteria_type) = (1, 'designation')
   OR (criteria_id, criteria_type) = (1, 'department')
```
However a more conventional approach would be to simplify that somewhat as the condition set for criteria_id is constant, so
```
WHERE criteria_id = 1
  AND criteria_type IN ('designation','department')
```
IN is a syntax shortcut, if expanded it would be the same as this:
```
WHERE criteria_id = 1
  AND (
        criteria_type = 'designation'
      OR
        criteria_type = 'department'
      )
```
Hopefully you can see how IN is useful in such situations.
Login or Signup to reply.

- user1191247
- November 12, 2023 at 11:56 am
- 0 votes
0
It is not completely clear what you are trying to do. Adding sample data and desired output to your question would help clarify.

If you are trying to find assessments that have both of those criteria, then perhaps this is what you are looking for:
```
SELECT assessment_id
FROM Assessment_Criteria
WHERE (criteria_id, criteria_type) = (1, 'designation')
   OR (criteria_id, criteria_type) = (1, 'department')
GROUP BY assessment_id
HAVING COUNT(DISTINCT criteria_id, criteria_type) = 2;
```
Given this sample data:

assessment_id criteria_id criteria_type

1 1 designation

2 1 department

3 1 designation

3 1 department

the above query will return:

assessment_id

3

If you have more criteria for your query but not all will necessarily be met, you can do something like:
```
SELECT assessment_id
FROM Assessment_Criteria
WHERE (criteria_id, criteria_type) = (1, 'designation')
   OR (criteria_id, criteria_type) = (1, 'department')
   OR (criteria_id, criteria_type) = (2, 'a_n_other')
   OR (criteria_id, criteria_type) = (2, 'a_n_other2')
   OR (criteria_id, criteria_type) = (3, 'something')
GROUP BY assessment_id
ORDER BY COUNT(DISTINCT criteria_id, criteria_type) DESC;
```
This will order the result set with the assessments meeting the most criteria at the top.
Login or Signup to reply.