File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Complicated SQL query help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Complicated SQL query help" Watch "Complicated SQL query help" New topic
Author

Complicated SQL query help

Phil Chuang
Ranch Hand

Joined: Feb 15, 2003
Posts: 251
Say I have the following data model:
Table A -0..m-> Table B -0..m-> Table C
Doing the sql for this is not hard, just joining tbls A->B and B->C. The problem is, that there are a lot of different Table Bs and Table Cs, and the size of the data returned grows exponentially. The last query of this kind that I ran returned 6+ megabytes, and 90% of which is redundant.

The Question:
Is there a way in SQL to get rid of the redundant data, so it would be like the following

Normally, this wouldn't be a problem - I'd do it programmatically with a loop and lose all the redundancy - however, in this case, my hands are tied:
The only sql queries I can do are "1-step" - so I can't get the results of A->B and use that to get B->C results. I have absolutely no programmatic control over the sql execution.
[ January 05, 2004: Message edited by: Phil Chuang ]
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15286
    
    6

Have you tried the distinct keyword?

The SELECT DISTINCT Statement
The DISTINCT keyword is used to return only distinct (different) values.
The SELECT statement returns information from table columns. But what if we only want to select distinct elements?
With SQL, all we need to do is to add a DISTINCT keyword to the SELECT statement:
Syntax
SELECT DISTINCT column_name(s)
FROM table_name



GenRocket - A Test Data Generation Platform
Phil Chuang
Ranch Hand

Joined: Feb 15, 2003
Posts: 251
That doesn't work because all the rows are distinct anyway. There are no duplicate rows. There is redundancy between rows, but not total redundancy (duplication).
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29249
    
139

Phil,
If every single value in column A is the same, you could retrieve it in a separate query. While this would introduce an extra network call, the data transfer for so many rows may take longer. Also, the queries might each be simpler/faster if you split it.
Also, keep your eyes out for any data that absolutely doesn't need to be transfered. Sometimes queries have the search criteria in the result set. Since the caller already has this information, it is not necessary to get it again.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Complicated SQL query help
 
Similar Threads
the algrithm in my homework
Header, Footer with dynamic data
Operator precedence and associativity confusion........
Combinations Algorithm
Tree Stored Procedure