我试图使用左连接获取3个表数据。表名称为tblOffer,tblCompanyDetails和tblFavourite。
SELECT tblOffer.OID, tblOffer.OfferCategory,
tblOffer.OfferTitle,tblOffer.OfferDetails,
tblOffer.ProductImagePath,
tblOffer.IsExciting,
tblOffer.ExpiringDate,
tblCompanyDetails . *,
tblFavourite.IsFavourite
FROM tblOffer
LEFT JOIN tblCompanyDetails ON tblOffer.CID = tblCompanyDetails.CID
LEFT JOIN tblFavourite ON tblOffer.OID = tblFavourite.OID
WHERE tblOffer.OfferCategory = 'shopping'
tblFavourite中没有行。因此tblFavourite.IsFavourite值为null。
我还有一个条件可以从tblFavourite获取数据。
SELECT tblOffer.OID,
tblOffer.OfferCategory,
tblOffer.OfferTitle,
tblOffer.OfferDetails,
tblOffer.ProductImagePath,
tblOffer.IsExciting,
tblOffer.ExpiringDate,
tblCompanyDetails . *,
tblFavourite.IsFavourite
FROM tblOffer
LEFT JOIN tblCompanyDetails ON tblOffer.CID = tblCompanyDetails.CID
LEFT JOIN tblFavourite ON tblOffer.OID = tblFavourite.OID
WHERE tblOffer.OfferCategory = 'shopping'
AND tblFavourite.UID = 1
请注意我写的条件.. tblFavourite.UID = 1最后。我知道条件是错误的。
如果不存在行,如何获取这三个表和tblFavourite.IsFavourite = 0
最佳答案
通过将tblFavourite.UID=1
放在WHERE
子句中,可以有效地将LEFT JOIN
上的tblFavourite
转换为INNER JOIN
。如果将该条件移到ON
子句中,而不是在WHERE
子句中,那么即使tblOffer
中没有匹配的行,您仍将从主表(tblFavourites
)中获取行。
至于用NULL
代替0
的tblFavourite.IsFavourite
,可以使用COALESCE()
函数来完成。
这样的事情应该为您工作:
SELECT tblOffer.OID,
tblOffer.OfferCategory,
tblOffer.OfferTitle,
tblOffer.OfferDetails,
tblOffer.ProductImagePath,
tblOffer.IsExciting,
tblOffer.ExpiringDate,
tblCompanyDetails.*,
COALESCE(tblFavourite.IsFavourite,0)
FROM tblOffer
LEFT JOIN tblCompanyDetails ON tblOffer.CID = tblCompanyDetails.CID
LEFT JOIN tblFavourite ON tblOffer.OID=tblFavourite.OID AND tblFavourite.UID=1
WHERE tblOffer.OfferCategory='shopping'