frobak frobak - 12 days ago 8
MySQL Question

Can I use IF or CASE on a where_in MySQL query based on value of select

Right, basically what I need is a different WHERE_IN clause based on the value of a column in the same table, and hence the same query.

So looking at the below query. The r.assigned_type is either a 1 or a 2, and I need to have either/or the r.assigned_id IN clause.

Can i do this?

SELECT `r`.`risk_id`, `r`.`assigned_type`, `r`.`risk_type_id`, `r`.`risk_name`, `r`.`next_review`, `r`.`last_review`, `r`.`status`, `a`.`area_name`, `u`.`first_name`, `u`.`last_name`
FROM `risk_assessments` `r`
LEFT JOIN `users` `u` ON `u`.`id` = `r`.`assigned_id`
LEFT JOIN `areas` `a` ON `a`.`area_id` = `r`.`assigned_id`
WHERE `r`.`org_id` = '3'
AND `r`.`risk_type_id` = '1'
AND `r`.`assigned` = '1'
AND `u`.`privilege_level` <= '7'
AND `r`.`assigned_id` IN('5', '10', '11', '12', '13', '14', '15')
OR `r`.`assigned_id` IN('9', '14')


Or do i need a select sub query?

Or even worse do I need to reformat the table, db structure and code?

What I would like to do is something like this (shortened query for example purposes):

SELECT `r`.`risk_id`, `r`.`assigned_type`, `r`.`risk_type_id`
FROM `risk_assessments` `r`
WHERE `r`.`org_id` = '3'
AND `r`.`assigned` = '1'
AND `u`.`privilege_level` <= '7'
CASE WHERE r.assigned_type = 2
`r`.`assigned_id` IN('5', '10', '11', '12', '13', '14', '15')
ELSE
`r`.`assigned_id` IN('9', '14')

Answer

It looks like you could use simple AND/OR with some brackets, like:

SELECT `r`.`risk_id`, `r`.`assigned_type`, `r`.`risk_type_id`
FROM `risk_assessments` `r`
WHERE `r`.`org_id` = '3'
AND `r`.`assigned` = '1'
AND `u`.`privilege_level` <= '7'
AND ((`r`.`assigned_type` = 2
     AND `r`.`assigned_id` IN('5', '10', '11', '12', '13', '14', '15'))
     OR `r`.`assigned_id` IN('9', '14'))