MySQL Grouping OR and AND clauses

Question

So the producer table schema is like this:

producer_id = int PK
producer_name = varchar
is_restaurant = boolean
is_farm = boolean
is_distributor = boolean

and I want a query that does this:

select all producers whose producer_id are in (11895, 11976, 11457) which is either a farm, a restaurant or a distributor

I have this:

SELECT `pd`.`producer_id` AS producer_id, `pd`.`producer` AS producer, `pd`.`is_restaurant`, `pd`.`is_restaurant_chain`, `pd`.`is_farm`, `pd`.`is_farmers_market`, `pd`.`is_distributor`, `pd`.`is_manufacture`, `ad`.`address_id`, `ad`.`address`, `ad`.`state_id`, `ad`.`city`, `ad`.`city_id`, `ad`.`country_id`, `ad`.`zipcode`, `ad`.`latitude`, `ad`.`longitude`, `cu`.`custom_url` AS custom_url
FROM (`producer` AS pd)
JOIN `address` AS ad ON `pd`.`producer_id`=`ad`.`producer_id`
LEFT JOIN `custom_url` AS cu ON `cu`.`producer_id`=`pd`.`producer_id`
WHERE `is_farm` = 1
OR `is_distributor` = 1
OR `is_restaurant` = 1
AND `pd`.`producer_id` IN ('181176', '181180', '176080', '5') 
GROUP BY `ad`.`address_id`
LIMIT 10

but the query only applies the WHERE conditions for is_farm, is_distributor and is_restaurant and does not apply the AND clause for producer_id.

I was wondering if I could use a subquery to do "is either a restaurant a distributor or a farm" and just insert it in the query with an AND clause.

What can I do to solve this?

Answer 1

Your problem is that in SQL, AND takes precedence over OR, so you just need brackets:

SELECT `pd`.`producer_id` AS producer_id, `pd`.`producer` AS producer, `pd`.`is_restaurant`, `pd`.`is_restaurant_chain`, `pd`.`is_farm`, `pd`.`is_farmers_market`, `pd`.`is_distributor`, `pd`.`is_manufacture`, `ad`.`address_id`, `ad`.`address`, `ad`.`state_id`, `ad`.`city`, `ad`.`city_id`, `ad`.`country_id`, `ad`.`zipcode`, `ad`.`latitude`, `ad`.`longitude`, `cu`.`custom_url` AS custom_url
FROM (`producer` AS pd)
JOIN `address` AS ad ON `pd`.`producer_id`=`ad`.`producer_id`
LEFT JOIN `custom_url` AS cu ON `cu`.`producer_id`=`pd`.`producer_id`
WHERE (`is_farm` = 1 -- NOTE: Opening bracket to bracket up your OR terms together
OR `is_distributor` = 1
OR `is_restaurant` = 1) -- NOTE: Closing bracket
AND `pd`.`producer_id` IN ('181176', '181180', '176080', '5') 
GROUP BY `ad`.`address_id`
LIMIT 10

Corollary:

In SQL A OR B OR C AND D is parsed as (A OR (B OR (C AND D))). Applying brackets will correct this common error, ie (A OR B OR C) AND D.

Remember: When mixing AND and OR terms, always use brackets!

Answer 2

This should do the trick:

SELECT `pd`.`producer_id` AS producer_id, `pd`.`producer` AS producer, `pd`.`is_restaurant`, `pd`.`is_restaurant_chain`, `pd`.`is_farm`, `pd`.`is_farmers_market`, `pd`.`is_distributor`, `pd`.`is_manufacture`, `ad`.`address_id`, `ad`.`address`, `ad`.`state_id`, `ad`.`city`, `ad`.`city_id`, `ad`.`country_id`, `ad`.`zipcode`, `ad`.`latitude`, `ad`.`longitude`, `cu`.`custom_url` AS custom_url
FROM (`producer` AS pd)
JOIN `address` AS ad ON `pd`.`producer_id`=`ad`.`producer_id`
LEFT JOIN `custom_url` AS cu ON `cu`.`producer_id`=`pd`.`producer_id`
WHERE `pd`.`producer_id` IN ('181176', '181180', '176080', '5')
AND (`is_distributor` = 1 OR `is_restaurant` = 1 OR `is_farm` = 1)
GROUP BY `ad`.`address_id`
LIMIT 10