• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Devaka Cooray
  • Tim Cooke
Sheriffs:
  • Rob Spoor
  • Liutauras Vilda
  • paul wheaton
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Mikalai Zaikin
  • Carey Brown
  • Piet Souris
Bartenders:
  • Stephan van Hulst

Complicated SQL query help

 
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).
 
author & internet detective
Posts: 41995
911
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Morning came much too soon and it brought along a friend named Margarita Hangover, and a tiny ad.
Gift giving made easy with the permaculture playing cards
https://coderanch.com/t/777758/Gift-giving-easy-permaculture-playing
reply
    Bookmark Topic Watch Topic
  • New Topic