开发者

SQL show record when join has no records

开发者 https://www.devze.com 2023-01-16 01:10 出处:网络
SELECTtblProducts.productName, tblProducts.basePrice, tblProductOptions.optionDescription FROMtblProducts CROSS JOIN tblProduct开发者_Go百科Options
SELECT     tblProducts.productName, 
           tblProducts.basePrice, 
           tblProductOptions.optionDescription
FROM       tblProducts CROSS JOIN tblProduct开发者_Go百科Options
WHERE      (tblProducts.ID = 3) AND (tblProductOptions.ID = 5)

If (tblProductOptions.ID = 5) then it works, there is an option with ID = 5. If it's (tblProductOptions.ID = 99999) then it returns nothing, as there is no option with ID = 99999.

I would like to modify the statement so that it still returns the product record if an invalid option ID is passed to it! Is this possible?

Thanks!


A CROSS JOIN is a cartesian product .. probably not what you are looking for. I would suggest INNER JOIN instead


Change CROSS JOIN to LEFT JOIN, and (because the WHERE limits the results to NON nulls) change your WHERE to be

WHERE (tblProducts.ID = 3) 
AND   (tblProductOptions.ID = 5 OR tblProductOptions.ID IS NULL) 
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号