Home » SQL & PL/SQL » SQL & PL/SQL » Minus question
Minus question [message #353433] Mon, 13 October 2008 22:02 Go to next message
DrBallard
Messages: 20
Registered: October 2008
Junior Member
Hey there, I've got to query the books that are available in BRANCH 1, but also those that are not available in branch 4

here's what I did

select TITLE, BR.BRANCH_NUM
from HENRY.BOOK B, HENRY.INVENTORY I, HENRY.BRANCH BR
where BR.BRANCH_NUM = I.BRANCH_NUM
and I.BOOK_CODE = B.BOOK_CODE
and BR.BRANCH_NUM = '1'
MINUS
(select TITLE, BR.BRANCH_NUM
from HENRY.BOOK B, HENRY.INVENTORY I, HENRY.BRANCH BR
where BR.BRANCH_NUM = I.BRANCH_NUM
and I.BOOK_CODE = B.BOOK_CODE
and BR.BRANCH_NUM = '4')


BUT the minus thing doesn't work : give the same resultats as without the minus
Thanks!
Re: Minus question [message #353438 is a reply to message #353433] Mon, 13 October 2008 22:26 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Why no more Dollar offer this time?

What do you mean by "doesn't work" ? If it gives the same result as the result without minus , it means no records ( books in your case) are common in branch1 and branch4 .

ie, None of books is Branch1 is present in Branch4

Thumbs Up
Rajuvan.
Re: Minus question [message #353440 is a reply to message #353438] Mon, 13 October 2008 22:34 Go to previous messageGo to next message
DrBallard
Messages: 20
Registered: October 2008
Junior Member
Yeah well nobody seemed to care about the money but it's still on
If you could come talk over msn it would be very useful... thanks
dr_ballard0002@hotmail.com
Re: Minus question [message #353445 is a reply to message #353433] Mon, 13 October 2008 22:38 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
I dont think... there is any problem in this query.
Re: Minus question [message #353448 is a reply to message #353433] Mon, 13 October 2008 22:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Sorry DrBallard (Dollar Ballard) Smile

I am so busy to deal with money now. Actually not even interested. We are here for Voluntary service.

Thumbs Up
Rajuvan.
Re: Minus question [message #353469 is a reply to message #353433] Tue, 14 October 2008 00:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
post a test case of you want a real solution. That means

Quote:
table creates
inserts
query results of your own test first
query results from each query run indepently to show there is really data

I am with other poster, on the surface the query looks fine. You could be getting same results because there simply are no branch 4 books comming back in your branch 4 query, either because there is no data, or because there is an error in it.

You should be able to figure that out by running the branch 4 piece and seeing what you get.

Kevin
Re: Minus question [message #353570 is a reply to message #353433] Tue, 14 October 2008 03:54 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Remove br.branch_num from the select part of each query.
You've got it hard-coded to different values in each select - 1 and 4.
Minus only removes rows where all the selected column values are the same and your query ensures this can't happen.
Re: Minus question [message #353662 is a reply to message #353433] Tue, 14 October 2008 09:03 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
DOH!, "I am so stupid". What an blind squirl I am to not have seen that sorry OP.

Kevin
Previous Topic: declare variable to show date and tim in my parameter
Next Topic: help with query
Goto Forum:
  


Current Time: Mon Dec 05 18:53:47 CST 2016

Total time taken to generate the page: 0.08938 seconds