当数据不在其中之一时，PHP 连接三个表

Question

我正在努力将三个表连接在一起，但在其中一个表中不会有任何信息，除非用户打开我们发送给他们的电子邮件

我当前正在使用此 SQL，但它似乎无法正常工作

SELECT email.senton, customer.*, count(tracker.viewed) as viewed 
FROM email_sent AS email, customer_detail AS customer, email_tracker AS tracker 
WHERE email.customer_id = customer.customer_id 
    AND customer.Email = tracker.emailaddress 
    AND customer.LeadOwnerId = '{$this->userid}'

这是由于该表email_tracker 可能没有客户信息，除非客户已打开电子邮件

Answer 1

试试这个：

SELECT email.senton, customer.*, COUNT(tracker.viewed) as viewed 
FROM email_sent email INNER JOIN customer_detail customer
    ON email.customer_id = customer.customer_id
LEFT JOIN email_tracker tracker 
    ON customer.Email = tracker.emailaddress
WHERE customer.LeadOwnerId = '{$this->userid}'

LEFT JOIN子句用于始终获取左侧部分的列和右侧部分的列（如果它们存在于连接中）...

根据您的评论进行编辑：
尝试更改 COUNT(tracker.viewed) 部分，

CASE
    WHEN tracker.emailaddress IS NOT NULL THEN COUNT(tracker.viewed)
    ELSE 0
END
    as viewed 

我不确定它是否有效，我无法测试它，但请尝试一下

Answer 2

您想要的行为是LEFT OUTER JOIN（也称为LEFT JOIN）。如果其他表中不存在该行，它只是将值设置为 NULL。

SELECT email.senton, customer.*, COUNT(tracker.viewed) AS viewed
FROM email_sent AS email
    JOIN customer_detail AS customer USING(customer_id)
    LEFT OUTER JOIN email_tracker AS tracker ON customer.Email = tracker.emailaddress
WHERE customer.LeadOwnerId = '{$this->userid}'

PS：通常最好使用 JOIN 而不是笛卡尔积（您使用 , 所做的）。