SQL show record when join has no records

Question

SELECT     tblProducts.productName, 
           tblProducts.basePrice, 
           tblProductOptions.optionDescription
FROM       tblProducts CROSS JOIN tblProductOptions
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!

Answer 1

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) 

Answer 2

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