This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Query Optimization Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Query Optimization" Watch "Query Optimization" New topic
Author

Query Optimization

sudha swami
Ranch Hand

Joined: Apr 24, 2007
Posts: 186
Hi,
Instead of using 2 queries, i would like to use only one to get the output A and B. is there any way to do it.

select description as A
from Area
where status = 0;

select count(id) as B
from Area
where status =1;

regards
sudha
Muhammad Saifuddin
Ranch Hand

Joined: Dec 06, 2005
Posts: 1321



I didn't try it myself but hope this will work.


Saifuddin..
[Blog][Linkedin] How To Ask Questions On JavaRanch My OpenSource
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1082

Originally posted by Saif uddin:




On the first look this query is not going to work because this will also count the ids having status as 0. however the query below is counting only ids which have status 1

also the query will display the description having status = 1 which is not expected from first query.


Top of all I am not sure what is the requirement to make a single query because first query SELECT DESCRIPTION AS A FROM AREA WHERE STATUS = 0; will result in multiple rows and second query SELECT COUNT(ID) AS B FROM AREA WHERE STATUS =1; will result in one row only.

Thanks,
Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 32481
    
214

Why do you want to put it in one query?


[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
 
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link: http://aspose.com
 
subject: Query Optimization
 
It's not a secret anymore!