File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Is this query even possible? 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 "Is this query even possible?" Watch "Is this query even possible?" New topic

Is this query even possible?

Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1130

I have several columns in my query.

One is a select distinct and another is a count. The third Column is another count(*) BUT the WHERE clauses for all three queries are different.

I'm trying to display a unified listing of counts.

I tried to use a UNION, but I ended up with some rows having the data filled in and others that didn't (different parts of the UNION joining). There were no "dups" for the UNION to eliminate.

Any ideas on how to combine data like this with multiple where clauses?

I'd appreciate any and all ideas.


-- Mike
Michael Matola
Ranch Hand

Joined: Mar 25, 2001
Posts: 1793
LOL. Have you been following these
two threads?

Sometimes you can do queries like I think you're saying, sometimes not.

Whenever you're counting based on "different wheres" see if "case when .. then .. end" logic inside your count()s will do the trick.

Any and all ideas? Post some detailed table structures, sample data, and some queries you've tried.

Are you talking stuff like this?

(The "1" in "then 1" is a dummy value. Count() works on null vs. nonnull, so anything other than a null will do.)

(Sorry, I won't be around again until next week.)
[ June 23, 2006: Message edited by: Michael Matola ]
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 862
Post a query of what you are trying to do. Sounds like you may be trying to do something like the following which i have done in sybase. You can also combine with the previous posters 'case' approach. I didn't try to run, but should work - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1130
Because the queries for the counts operate on different tables, have different WHERE clauses and such, I'm thinking that a better approach would be to:


0. Get Connection

1. Create a temp table (CREATE TEMPORARY TABLE temp SELECT ...) with calculated fields for the values we can't create with a single query or a UNION.

2. Then iterate over the temp table issuing queries to UPDATE the calculated fields using queries that would have been (seemingly) impossible to do with a single query. Here, we would read whatever fields from the temp table we need to create the UPDATE statements to fill in the calculated fields in the temp table.

3. Display results.


This approach would give you ultimate flexibility but would require a bit more work.


steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 862
Post the sample implementation and it will be easier to make comments
I agree. Here's the link:
subject: Is this query even possible?
It's not a secret anymore!