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)
精彩评论