我有两个桌子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
。
但是,当我加入交易并计算在内时,其中一个房屋没有交易,也没有显示。
您必须使用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
您tblFuneralTransactions
在WHERE
条件上使用的列。如果的记录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))
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句