JOIN that doesn't exclude all records if one side is null

Question

I have a fairly conventional set of order entry tables divided by:

Orders OrdersRows OrdersRowsOptions

The record in OrderRowOptions is not created unless needed. When I create a set of joins like

select * from orders o 
  inner join OrdersRows r on r.idOrder = o.idOrder 
  inner join ordersrowsoptions ro on ro.idOrderRow = r.idOrderRow
where  r.idProduct = [foo]

My full resultset is blank if no ordersrowsoptions records exist for the given product.

what's the correct syntax to return records even if no records exist at one of the join clauses?

thx

Answer 1

LEFT JOIN / RIGHT JOIN.

Edit: yes, the following answer, given earlier, is correct:

select * from orders o  
  inner join OrdersRows r on r.idOrder = o.idOrder  
  left join ordersrowsoptions ro on ro.idOrderRow = r.idOrderRow 
where  r.idProduct = [foo] 

Answer 2

select * from orders o  
  inner join OrdersRows r on r.idOrder = o.idOrder  
  left join ordersrowsoptions ro on ro.idOrderRow = r.idOrderRow 
where  r.idProduct = [foo] 

Of course you should not use select * in any query but especially never when doing a join. The repeated fields are just wasting server and network resources.

Since you seem unfamiliar with left joins, you probably also need to understand the concepts in this: http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN

Answer 3

LEFT JOIN (or RIGHT JOIN) are probably what you are looking for, depending on which side of the join no rows may appear.

Answer 4

Interesting, do you want to get all orders that have that product in them? The other post is correct that you have to use LEFT or RIGHT OUTER JOINS. But if you want to get entire orders that have that product then you'd need a more complex where clause.