OraFAQ Forum: Training & Certification » left join on, incl a where

Home » Other » Training & Certification » left join on, incl a where (SQL*Plus, 10g Express, Win2K Pro)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

left join on, incl a where [message #280697]

Wed, 14 November 2007 09:41

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?

Attachment: Oracle-Henry.sql
(Size: 10.32KB, Downloaded 1294 times)

Report message to a moderator

Re: left join on, incl a where [message #280880 is a reply to message #280697]

Thu, 15 November 2007 01:34

Maaher
Messages: 7065
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

Report message to a moderator

Re: left join on, incl a where [message #281071 is a reply to message #280880]

Thu, 15 November 2007 11:27

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.

Report message to a moderator

Re: left join on, incl a where [message #281092 is a reply to message #281071]

Thu, 15 November 2007 12:49

Maaher
Messages: 7065
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

Report message to a moderator

Previous Topic:	syllabus required
Next Topic:	sql query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 15:06:44 CDT 2024