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.
2
Answers
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
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:
However a more conventional approach would be to simplify that somewhat as the condition set for criteria_id is constant, so
IN is a syntax shortcut, if expanded it would be the same as this:
Hopefully you can see how IN is useful in such situations.
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:
Given this sample data:
the above query will return:
If you have more criteria for your query but not all will necessarily be met, you can do something like:
This will order the result set with the assessments meeting the most criteria at the top.