Meaningless Drivel is fun!
The moose likes JDBC and Relational Databases and the fly likes Complicated SQL query help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Complicated SQL query help" Watch "Complicated SQL query help" New topic

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: 15302

Have you tried the distinct keyword?

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:
SELECT DISTINCT column_name(s)
FROM table_name

GenRocket - Experts at Building Test Data
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
author & internet detective

Joined: May 26, 2003
Posts: 33132

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.

[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 agree. Here's the link:
subject: Complicated SQL query help
It's not a secret anymore!