SQL存在或存在，
SQL存在或存在，
提问于 2022-04-17 14:49:49
我有三张桌子，
TCorporateSponsors-5赞助商
(intCorporateSponsorID，strCompanyName，strFirstName，strLastName)
TEventCorporateSponsorshipTypes
(intEventCorporateSponsorshipTypeID，intEventID，intCorporateSponsorshipTypeID，monTypeCost)
TEventCorporateSponsorshipTypeCorporateSponsors
(intEventCorporateSponsorshipTypeCorporateSponsorID，intEventCorporateSponsorshipTypeID，intCorporateSponsorID)
目标是归还所有赞助商，即使他们没有捐赠。我有一个公司的赞助人没有捐出第五个。
下面的代码返回了4位赞助商和捐款总额，但是第5位赞助商被忽略了。如何确保所有5都显示？
SELECT
TC.intCorporateSponsorID AS CorporateSponsorID,
TC.strCompanyName,
SUM(TECST.monTypeCost) AS TotalAmountSponsoredToDate
FROM
TCorporateSponsors AS TC
JOIN
TEventCorporateSponsorshipTypeCorporateSponsors AS TECSTCS
ON TC.intCorporateSponsorID = TECSTCS.intCorporateSponsorID
JOIN
TEventCorporateSponsorshipTypes AS TECST
ON TECST.intEventCorporateSponsorshipTypeID = TECSTCS.intEventCorporateSponsorshipTypeID
WHERE
TC.intCorporateSponsorID IN (1,2,3,4,5) -- There are a total of 5 Sponsors, number 5 did not donate. When I run only the 4 show up.
GROUP BY TC.intCorporateSponsorID, TC.strCompanyName其他我不知道该如何执行的想法：
WHERE EXISTS (SELECT 1
FROM TCorporateSponsors
WHERE TCorporateSponsors.intCorporateSponsorID BETWEEN 1 and 5)回答 1
Stack Overflow用户
发布于 2022-04-18 03:00:57
您可以在这里使用LEFT JOIN。
要么在两个联接上使用它，要么更好:在INNER JOIN中嵌套一个LEFT JOIN
SELECT
TC.intCorporateSponsorID AS CorporateSponsorID,
TC.strCompanyName,
SUM(TECST.monTypeCost) AS TotalAmountSponsoredToDate
FROM
TCorporateSponsors AS TC
LEFT JOIN
(
TEventCorporateSponsorshipTypeCorporateSponsors AS TECSTCS
INNER JOIN
TEventCorporateSponsorshipTypes AS TECST
ON TECST.intEventCorporateSponsorshipTypeID = TECSTCS.intEventCorporateSponsorshipTypeID
) ON TC.intCorporateSponsorID = TECSTCS.intCorporateSponsorID
GROUP BY
TC.intCorporateSponsorID,
TC.strCompanyName;注意，括号()是可选的，键是嵌套ON子句。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71902866
复制相关文章
相似问题
腾讯云开发者
