Home » Other » Training & Certification » left join on, incl a where (SQL*Plus, 10g Express, Win2K Pro)
left join on, incl a where [message #280697] Wed, 14 November 2007 09:41 Go to next message
ShinyPup
Messages: 2
Registered: November 2007
Location: Michigan
Junior Member
http://i2.photobucket.com/albums/y49/shinypuppy/SQL17.jpg
I've though it was:
select b.book_code, b.title, i.on_hand
from book b
left join inventory i
on i.book_code = b.book_code
where i.branch_num = '2'
order by b.book_code;
But that only gets the 15 true results not the 47 total I want. Any thoughts?
Re: left join on, incl a where [message #280880 is a reply to message #280697] Thu, 15 November 2007 01:34 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ShinyPup wrote on Wed, 14 November 2007 16:41

Any thoughts?
You're close. But the Where clause on the branch is breaking the outer join. You can use Oracle's proprietary syntax:
SELECT b.book_code
     , b.title
     , i.on_hand
FROM    book b
   ,    inventory i 
WHERE    i.branch_num(+) = 2
AND      i.book_code(+) = b.book_code
ORDER BY b.book_code
/
Or you can use the ANSI join syntax, and include the branch restriction in the join condition.

Out of curiosity: is this an Oracle question? You use non-standard data types and Oracle keywords in you table creation script.

MHE
Re: left join on, incl a where [message #281071 is a reply to message #280880] Thu, 15 November 2007 11:27 Go to previous messageGo to next message
ShinyPup
Messages: 2
Registered: November 2007
Location: Michigan
Junior Member
As far as I know it is, as it is an Oracle class. As far as the script, I just used what the book company gave us. I figured it out though by adding join condition as part of the join on statement.
Re: left join on, incl a where [message #281092 is a reply to message #281071] Thu, 15 November 2007 12:49 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It is valid Oracle SQL, that's right. Most people however would stick to NUMBER for numeric data types. I haven't seen much DECIMAL columns yet. It's not bad or anything, it's just not common.

MHE
Previous Topic: syllabus required
Next Topic: sql query
Goto Forum:
  


Current Time: Fri Dec 02 20:21:35 CST 2016

Total time taken to generate the page: 0.32871 seconds