像查询这样的 Mysql 查询在与其他表的 where 条件连接时不会发送准确的结果

Question

我有两个表用户和用户组。

我正在运行以下查询:-

SELECT `u`.`id`, `u`.`fname`, `u`.`lname`, `u`.`customer_id`, `u`.`email`, `u`.`partner_id`, `u`.`campaign_promocode`, `u`.`utm_medium`, `u`.`referrral_source`, `u`.`is_active`, `u`.`created_at`, `ug`.`group_id`
FROM (`users` as u)
JOIN `users_groups` as ug ON `ug`.`user_id` = `u`.`id`
WHERE `ug`.`group_id` =  '99'
OR `ug`.`group_id` =  '100'
AND `u`.`is_active` IN ('1', '2') 
AND  `u`.`fname`  LIKE '%sandeep%'
ORDER BY `u`.`id` desc
LIMIT 10

但是如果我在 where 子句 ug.group_id = '99' 中删除，上面的查询将返回错误的结果 OR ug.group_id = '100' 那我就对了。

数据库大约有 7K 行。

Answer 1

试试这个:

SELECT `u`.`id`, `u`.`fname`, `u`.`lname`, `u`.`customer_id`, `u`.`email`, `u`.`partner_id`, `u`.`campaign_promocode`, `u`.`utm_medium`, `u`.`referrral_source`, `u`.`is_active`, `u`.`created_at`, `ug`.`group_id`
FROM (`users` as u)
JOIN `users_groups` as ug ON `ug`.`user_id` = `u`.`id`
WHERE (`ug`.`group_id` =  '99' OR `ug`.`group_id` =  '100' )
AND `u`.`is_active` IN ('1', '2') 
AND  `u`.`fname`  LIKE '%sandeep%'
ORDER BY `u`.`id` desc
LIMIT 10