CODEWITHSUNDEEP
sqljoin
Tom Gullen
Tom Gullen

Reputation: 61729

SQL show record when join has no records

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!

Upvotes: 0

Views: 443

Answers (2)

guigui42
guigui42

Reputation: 2500

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

Upvotes: 3

cjk
cjk

Reputation: 46415

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)

Upvotes: 1

Related Questions