Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Complicated SQL query help

 
Phil Chuang
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
Phil Chuang
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic