DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: please help me with my outer joins before I break something

  1. #1
    Join Date
    Jan 2002
    Posts
    2

    Angry

    I was really excited about finally developing an Oracle application, and I believed in my heart that it was better than MSSQL. But this outer join crap...

    Here's the deal. There's a products table. Every product might have a category, might have a mediatype, might have a product author record that would link through to authors. Of course, a product might have none of the above. In MSSQL, the following works just fine (being ANSI compliant and all):

    SELECT [whatever]
    FROM (((Products p LEFT OUTER JOIN mediatypes m ON p.MediaTypeID = m.MediaTypeID) LEFT OUTER JOIN categories c ON p.CategoryID = c.CategoryID) LEFT OUTER JOIN productauthors pa ON p.ProductID = pa.ProductID) LEFT OUTER JOIN Authors a ON pa.AuthorID = a.AuthorID

    Apparently I'm going to have to write some endless number of views or something, but though I've searched the forums, I can't quite find the answer for this one. Can someone please help me before I have to hurt my poor innocent computer, which is after all only a proxy for the remote database?

    (oh, and I'm working on the other side of the world from my colleagues, and we only installed this recently, so I honestly don't know which Oracle version we're running, and they're asleep. But it doesn't seem to recognize ANSI join syntax at all, so I gather it is not 9i)

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    You use a (+) symbol on the side of the join where the missing rows are likely to be. You're query would look something like:

    SELECT p.*
    FROM Products p, mediatypes m, categories c,
    productauthors pa
    WHERE p.MediaTypeID = m.MediaTypeID (+)
    AND p.CategoryID = c.CategoryID (+)
    AND p.ProductID = pa.ProductID (+)
    AND pa.AuthorID = a.AuthorID (+)


Posting Permissions

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


Click Here to Expand Forum to Full Width