Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    38

    Unanswered: LEFT OUTER JOIN with WHERE

    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by tosa View Post
    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'

  3. #3
    Join Date
    Feb 2010
    Posts
    38
    Yes, that did it.

    Thanks a lot!

  4. #4
    Join Date
    Aug 2010
    Posts
    1
    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?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by eurekaplanet View Post
    This order in which it returns is essential for the the application to run properly.
    you must use an ORDER BY clause in the SELECT statement

    without the ORDER BY clause, your results are unpredictable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •