How can I do a successful LEFT OUTER JOIN with a WHERE constraint on the right table?
Consider this SQL statement:
"SELECT Qty, ItemCode, Description, IFNULL(Attributes.QtyValue,1) FROM PUB.QEvent
LEFT OUTER JOIN PUB.Attributes ON QEvent.Key=Attributes.Key
WHERE Attributes.Name= 'Item'"
It's simplified, but I think I get my point through. All items does not have an attribute. If I leave out WHERE I get rows I don't want from table Attributes, but when using WHERE I do not get the rows that don't have an attribute because of the WHERE constraint.
A left outer join the number of rows returned but not in the same order. If the number of columns in the select changes the order of results. It should not happens when you use a simple join. I have to choose ordering it in my sql. This order in which it returns is essential for the the application to run properly.
Is this the same result?