Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Posts
    9

    Unanswered: SQL Outer Joins problem

    Hi,

    I'm upgrading from SQL 2000 -> 2005 at the moment. Having an issue with some old Outer Join SP's which use the =* and *= operators.

    The issue i am having is this. Using this Procedure on the Pubs DB:
    Code:
    SELECT title, qty FROM titles, sales
    WHERE titles.title_id *= sales.title_id
    ORDER BY qty
    Works fine. I get 23 rows with 2 of them having NULL in qty.

    This also works, giving the same result:
    Code:
    SELECT title, qty 
    FROM titles
    LEFT OUTER JOIN sales
      ON titles.title_id = sales.title_id
    ORDER BY qty
    All looking good. Now, just say i want to limit the rows from the Right table. Lets just select Store #6380. Using the old *= operator:
    Code:
    SELECT title, qty 
    FROM titles, sales
    WHERE titles.title_id *= sales.title_id
    AND Stor_ID = 6380
    ORDER BY qty
    This works fine, giving me all 18 titles, with sales amounts next to two of them, the rest are null, which is the response I want. However, if I insert the same WHERE clause into the OUTER JOIN code:
    Code:
    SELECT title, qty 
    FROM titles
    RIGHT OUTER JOIN sales
      ON titles.title_id = sales.title_id
    WHERE Stor_Id = 6380
    ORDER BY title
    It seems to act like an Inner Join, not an Outer Join, giving me only rows that match on both sides. I can't seem to work out why this would do this. Am I overlooking something simple? Can someone give me some idea why it's not working?

    Thanks,

    Josh

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Store ID is a column in Titles right? If not then it is a different problem (easily sorted too).

    I've removed the where clause from your second query. Notice - they are not the same (HINT - the order by isn't the problem). If they were (and bit about Store ID is correct) your query would work.
    Code:
    SELECT title, qty FROM titles LEFT OUTER JOIN sales ON titles.title_id = sales.title_id ORDER BY qty
    Code:
    SELECT title, qty FROM titles RIGHT OUTER JOIN sales ON titles.title_id = sales.title_id ORDER BY title
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2002
    Posts
    9
    Thanks for your reply.

    Stor_ID is actually a column from the sales table. I want to be able to do a Right Outer Join, but restrict the data on the Right table of the join, in this case Sales. I would have thought using a WHERE clause would do the trick, but that doesn't seem to work in this case.

    Yes - the ORDER BY is different, but this is of no significance. The result set should contain the same data either way.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - you know there is a difference between a right outer join and wanting to restrict the rows on the right table yes? BTW - there are a few developers\ DBAs who don't believe in ever using right joins because they can do little more than cause confusion and I am starting to feel that way too.

    Anyway - I think you are looking for this (otherwise please could you state exactly what you are looking for in natural English):
    Code:
    SELECT title, qty
    FROM titles LEFT OUTER JOIN sales ON titles.title_id = sales.title_id AND sales.Store_ID = 6380 ORDER BY title
    The problem was that the where clause is applied on the result set of the join. Applying a where clause on the outer table effectively turns your join into an inner join. Your join was wrong too which didn't help. By applying the condition in the join the data in the outer table is restricted before the join is applied.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Hi,

    >> I see Pootle Fump beat me to it...

    Try the following...
    Code:
    SELECT title, qty 
    FROM titles
    RIGHT OUTER JOIN sales ON titles.title_id = sales.title_id
                                    AND Stor_Id = 6380
    WHERE 1=1
    ORDER BY title
    By putting the clause in de JOIN you're limiting the set with wich to join. When you put it in the WHERE-clause you're limiting the outcome of the join.

    NB. I added the dummy "WHERE 1=1" to explicitly show that de clause is in the JOIN and not in de WHERE.

    Grtz, Lex

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Lexiflex
    >> I see Pootle Fump beat me to it...
    If you can't beat my keyboard pounding there isn't much hope

    Lexiflex - do you think the join needs to be a LEFTY rather than a RIGHTY?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by pootle flump
    If you can't beat my keyboard pounding there isn't much hope ?
    I know, I'm hopeless

    Quote Originally Posted by pootle flump
    Lexiflex - do you think the join needs to be a LEFTY rather than a RIGHTY?
    You're right, it should be a LEFTy (assuming titles.title and sales.qty and wanting to have all titles but only sold quantities for sales.Store_Id 8380).

Posting Permissions

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