sql - 计数连接表

Question

我有两个表 tblFuneralHomes 和 tblFuneralTransactions。一张表可能有很多事务。我需要返回用户的 funeralHomes 并计算交易，但如果 funeralHomes 没有交易，则不会在结果中返回。

Select tfh.funeral_home_guid, tfh.funeral_home_name, tfh.reference_key, count(tft.id) as counts from tblFuneralHomes tfh
inner join tblFuneralTransactions tft on tft.funeral_home_guid = tfh.funeral_home_guid where (tft.canceled=0 and tft.completed=0)
and tfh.funeral_home_guid in (select funeral_home_guid 
from tblDirectorHomes
where director_id = '1789a3ae-95e5-4719-ac09-bd1ada01dd5b') 
group by tfh.funeral_home_guid, tfh.funeral_home_name, tfh.reference_key

这是没有加入的结果

我知道当前用户有 3 个 funeralHomes。

但是当我加入交易并计算时，其中一个房屋没有交易并且没有显示。

Answer 1

您必须使用 LEFT JOIN 而不是 INNER JOIN 所以 tblFuneralHomes 的所有记录都会被获取:

SELECT tfh.funeral_home_guid, tfh.funeral_home_name, tfh.reference_key, COUNT(tft.id) AS counts 
FROM tblFuneralHomes tfh LEFT JOIN tblFuneralTransactions tft ON tft.funeral_home_guid = tfh.funeral_home_guid 
WHERE (tft.funeral_home_guid IS NULL OR (tft.canceled = 0 AND tft.completed = 0))
    AND tfh.funeral_home_guid IN (
        SELECT funeral_home_guid 
        FROM tblDirectorHomes
        WHERE director_id = '1789a3ae-95e5-4719-ac09-bd1ada01dd5b'
    )
GROUP BY tfh.funeral_home_guid, tfh.funeral_home_name, tfh.reference_key

您正在 WHERE 条件下使用 tblFuneralTransactions 的列。如果 tblFuneralHomes 的记录没有 tblFuneralTransactions(不可用)tblFuneralTransactions 记录的所有列值都是 NULL .因此以下条件为假:tft.canceled = 0 AND tft.completed = 0。

要包含 tblFuneralHomes 的所有记录，您需要允许 tblFuneralTransactions 的列为 NULL。所以你必须替换这个条件

(tft.canceled = 0 AND tft.completed = 0)

以下内容

(tft.funeral_home_guid IS NULL OR (tft.canceled = 0 AND tft.completed = 0))