Geek Answers Handbook

Grouping MySQL OR and AND

Thus, the scheme of the table of manufacturers is as follows:

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

and I need a query that does this:

select all manufacturers whose producer_id is located in (11895, 11976, 11457), whether it is a farm, restaurant or distributor

I have it:

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 clauses for is_farm, is_distributor and is_restaurant and does not apply the AND clause for the producer_name.

I was wondering if I can use the subquery to make "this is either a distributor restaurant or a farm," and just paste it into the query with the AND clause.

What can I do to solve this problem?

+5
source share
2 answers

, SQL AND OR, :

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

:

SQL A OR B OR C AND D (A OR (B OR (C AND D))). , .. (A OR B OR C) AND D.

: AND OR !

+10

:

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
+2

More articles:


All Articles