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 ]
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, 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.