mysql - 选择联接中*每*条记录包含字符串的位置

Question

我有以下 MySQL 表:

n_companies:

id     company_name1      Company A2      Company B

n_contacts:

id     company_id     contact_name1      1              John2      1              Frank3      2              Bobby4      2              Sophie

n_custom:

id   custom_type   custom_type_id    gender   fav_colour1    contacts      1                 male     red2    contacts      2                 male     red3    contacts      3                 male     red4    contacts      4                 female   green

我正在创建一个搜索 UI，允许人们搜索任何/每条/没有包含字符串的记录。

这是我认为应该处理的查询:

查找每个联系人 (n_contacts) 都是男性的公司 (n_companies)(存储在自定义字段表 n_custom 中):

SELECT     `n_companies`.`id`,    `n_companies`.`company_name`FROM `n_companies`LEFT JOIN `n_contacts`    ON ( `n_contacts`.`company_id` = `n_companies`.`id` )LEFT JOIN `n_custom` AS `custom_contacts`    ON ( `n_contacts`.`id` = `custom_contacts`.`custom_type_id`    AND `custom_contacts`.`custom_type` = 'contacts' )WHERE      EXISTS (        SELECT `id`        FROM   `n_custom`        WHERE  `n_custom`.`custom_type_id` = `n_contacts`.`id` AND `n_custom`.`custom_type` = 'contacts'        AND `n_custom`.`gender` LIKE '%male%'    )    AND NOT EXISTS (        SELECT `id`        FROM   `n_custom`        WHERE  `n_custom`.`custom_type_id` = `n_contacts`.`id` AND `n_custom`.`custom_type` = 'contacts'        AND `n_custom`.`gender` NOT LIKE '%male%'    )GROUP BY    `n_companies`.`id`ORDER BY    `n_companies`.`company_name` ASC

我正在寻找上述查询以仅返回 Company A，因为它的两个联系人都是男性。 B公司有1男1女

注意事项:

• n_companies 和 c_contacts 是默认创建的表标准字段。 n_custom 是用户可以创建自己的地方用于存储有关各种公司相关表的信息的字段。
• 我无法在 JOIN 中执行此搜索，因为可能还有另一个 OR搜索独立于上述查询的 n_contacts。

谁能帮我解决为什么我的查询不起作用？

提前谢谢你。

编辑我刚刚意识到我的“否”记录也不起作用。在我看来，这应该返回零行，但事实并非如此:

SELECT     `n_companies`.`id`,    `n_companies`.`company_name`FROM `n_companies`LEFT JOIN `n_contacts`    ON ( `n_contacts`.`company_id` = `n_companies`.`id` )LEFT JOIN `n_custom` AS `custom_contacts`    ON ( `n_contacts`.`id` = `custom_contacts`.`custom_type_id`    AND `custom_contacts`.`custom_type` = 'contacts' )WHERE      NOT EXISTS (        SELECT `id`        FROM   `n_custom`        WHERE  `n_custom`.`custom_type_id` = `n_contacts`.`id` AND `n_custom`.`custom_type` = 'contacts'        AND `n_custom`.`fav_colour` NOT LIKE '%red%'    )GROUP BY    `n_companies`.`id`ORDER BY    `n_companies`.`company_name` ASC

Answer 1

您正在寻找联系人符合特定条件的公司。汇总每个公司的标准并查看 HAVING 子句中的结果。一个例子:

select *
from companies
where id in
(
  select company_id
  from n_contacts con
  join n_custom cus on cus.custom_type_id = con.id and cus.custom_type = 'contacts'
  group by company_id
  having sum(cus.gender = 'male') = count(*) -- all contacts are male
     and sum(cus.fav_colour = 'red') = 2 -- at least two contacts like red
     and sum(cus.fav_colour = 'green') = 0 -- no contact likes green
     and sum(con.contact_name = 'John') > 0 -- at least one contact is named John
);