LEFT OUTER JOIN with WHERE

tosa · #1 (**permalink**) 04-14-10, 07:16

I'm stuck again!

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.

shammat · #2 (**permalink**) 04-14-10, 08:08

Quote:

Originally Posted by tosa

I'm stuck again!

How can I do a successful LEFT OUTER JOIN with a WHERE constraint on the right table?

The WHERE clause essentially turns the OUTER JOIN into an INNER JOIN.

You need to move the "where" clause into the JOIN condition:
Something like this:

Code:

SELECT Qty, 
       ItemCode, 
       Description, 
       IFNULL(Attributes.QtyValue,1) 
FROM PUB.QEvent 
   LEFT OUTER JOIN PUB.Attributes 
        ON QEvent.Key=Attributes.Key AND Attributes.Name= 'Item'

tosa · #3 (**permalink**) 04-14-10, 08:15

Yes, that did it.

Thanks a lot!

eurekaplanet · #4 (**permalink**) 08-17-10, 17:41

Hi,
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?