mysql 如何获取所有联系人的所有相关电子邮件帐户都标记为无效?

ygya80vv  于 2023-11-16  发布在  Mysql
关注(0)|答案(1)|浏览(118)

考虑到以下方案

CREATE TABLE contacts (
    id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    PRIMARY KEY(Id)
);

CREATE TABLE email_addresses (
    id INT NOT NULL AUTO_INCREMENT,
    email_address VARCHAR(255) NOT NULL,
    invalid_email tinyint(1) NOT NULL,
    PRIMARY KEY(Id)
);

CREATE TABLE email_addr_bean_rel (
    id INT NOT NULL AUTO_INCREMENT,
    email_address_id VARCHAR(255) NOT NULL,
    contact_id VARCHAR(255) NOT NULL,
    bean_module VARCHAR(255) NOT NULL,
    PRIMARY KEY(Id)
);

字符串
我想让所有的联系人,他们所有的相关电子邮件的invalid_email字段的值为false
鉴于以下记录

INSERT INTO contacts
    (id, first_name, last_name)
VALUES
    (1, 'Jill', 'Valentine'),
    (2, 'Ada', 'Wong'),
    (3, 'Leon', 'Kennedy')
;

INSERT INTO email_addresses
    (id, email_address, invalid_email)
VALUES
    (1, '[email protected]', true),
    (2, '[email protected]', true),
    (3, '[email protected]', false),
    (4, '[email protected]', false)
;

INSERT INTO email_addr_bean_rel
    (id, email_address_id, contact_id, bean_module)
VALUES
    (1, '1', 1, 'contacts'),
    (2, '2', 2, 'contacts'),
    (3, '3', 2, 'contacts'),
    (4, '4', 3, 'contacts')
;


了预期的结果
(1,'Jill','Valentine'),
因为艾达有两个电子邮件帐户,一个有效,一个无效,条件是与联系人关联的所有电子邮件帐户必须无效。
我所采取的方法如下

SELECT eabr.contact_id
    FROM email_addr_bean_rel eabr
    JOIN email_addresses ea ON eabr.email_address_id = ea.id
    WHERE ea.invalid_email = false


我试图获得无效帐户第一,它可能是一个子查询。但我明白我必须首先获得一个摘要,其中所有的帐户都是无效的
提前感谢任何想法

w46czmvw

w46czmvw1#

您可以GROUP BY联系人并检查MIN(invalid_email)

SELECT c.*
FROM contacts c
JOIN email_addr_bean_rel eabr ON c.id = eabr.contact_id
JOIN email_addresses ea ON eabr.email_address_id = ea.id
GROUP BY c.id
HAVING MIN(ea.invalid_email) = 1;

字符串
产出:
| ID|名字|姓氏(_N)|
| --|--|--|
| 1 |吉尔|情人|
这里有一个db<>fiddle

相关问题