Click here to Skip to main content
15,899,679 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
i have a database. after MINUS operation in SQL i get certain result but i am unable to get top 1 element from it. can anyone pls help....

here is my query
select top 1 Book_Stock.StockId from Book_Stock,Issue_Book where  Book_Stock.BookId='99'
except
select Book_Stock.StockId from Book_Stock,Issue_Book  where Book_Stock.StockId = Issue_Book.StockId

this query gets stockid which is not in bookstock and issue book.

result is like<br />
-----------<br />
stock id<br />
173<br />
174<br />
176<br />
177<br />
186<br />
187


now how can i get the top 1 stockid from the above result, in the single above query?
Posted
Updated 19-Jul-11 0:21am
v3
Comments
R. Giskard Reventlov 18-Jul-11 7:45am    
Need to see what you are doing as my powers of telepathy have been on the wane recently...
Manfred Rudolf Bihy 18-Jul-11 7:48am    
Let's wait till Griff drops by. He's usually quite good at that. ;)
OriginalGriff 18-Jul-11 8:44am    
I heard that!
[no name] 18-Jul-11 8:20am    
Show us your query and also tell us you are unable to do it?
greatreddevil 19-Jul-11 5:52am    
i have updated my question.

It is difficult to help you here, without seeing your actual query, and a relevant subset of the two data sets you are operating on.
However, the most common mistake is to get the order of the two statements the wrong way round: Since MINUS returns only those records which ARE in the first set and NOT in the second, (ignoring all those that ARE in the second and NOT in the first) if you swap the order, it is likely to return an empty dataset.

First things first: run your first statement alone into a DataTable, then your second statement alone into another. Compare the two tables (if necessary, dump them into DataGridViews or use DataTable.WriteXML method to let you analyse them offline with a text editor or similar)

If it looks like there should be records after the MINUS, we need to see the queries and a relevant data subset.

OP Added SQL statement:

select top 1 Book_Stock.StockId from Book_Stock,Issue_Book where Book_Stock.BookId='99'
except
select Book_Stock.StockId from Book_Stock,Issue_Book where Book_Stock.StockId = Issue_Book.StockId


That won't work - the first SELECT will only return the top row in the table, which is then checked against the exclusion list. If you want to return the top result of the combined exclusion, you need to nest the queries:
SELECT TOP 1 * FROM myTable WHERE iD IN 
  (
   SELECT iD FROM myTable WHERE iD > 5 
   EXCEPT
   SELECT iD FROM myTable WHERE iD < 10
  )
You will have to jiggle it to match what you want to do exactly, but that's the principle.
 
Share this answer
 
v2
Comments
greatreddevil 19-Jul-11 5:51am    
Sry for late reply. I have updated my question. Plz help
OriginalGriff 19-Jul-11 6:19am    
Answer updated
Try this.
Select Top 1 Book_Stock.StockId from (select Book_Stock.StockId from Book_Stock,Issue_Book where  Book_Stock.BookId='99'
except
select Book_Stock.StockId from Book_Stock,Issue_Book  where Book_Stock.StockId = Issue_Book.StockId) AS A
 
Share this answer
 
v2
Comments
greatreddevil 19-Jul-11 7:21am    
thanks it worked :)
Toniyo Jackson 19-Jul-11 7:23am    
You are welcome :) Vote and accept the answer.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900