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):
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)
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:
FROM Products p, mediatypes m, categories c,
WHERE p.MediaTypeID = m.MediaTypeID (+)
AND p.CategoryID = c.CategoryID (+)
AND p.ProductID = pa.ProductID (+)
AND pa.AuthorID = a.AuthorID (+)
Click Here to Expand Forum to Full Width