Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Gathering group info in one row

Gathering group info in one row

From: Jochen Wiedmann <jochen.wiedmann_at_freenet.de>
Date: 22 Sep 2004 01:01:08 -0700
Message-ID: <c66df65e.0409220001.43b620fe@posting.google.com>


Hi,

we have a multitude of queries that all follow the same scheme, and are, unfortunately, tediously slow. Thus, it quite makes some sense to optimize them.

The query combines two tables, that are in an 1:n relation. I'll call them A
and B:

      Table A       id    name
                    0     Someone
                    1     Anyone

      Table B       idA    group   name
                    0      0       Jim
                    0      0       George
                    0      1       Jack
                    0      2       Sam

There is a predefined set of groups (about six or seven). For any group and type, there are between 0 and 6 entries in table B.

The desired query result should look like this:

      id   name     name0   count0  name2   count2   name3  count3
      0    Someone  George  2       Sam      1       <null> 0
      1    Anyone   Bob     1       <null>   0       James  4

In words: Select certain rows from A (that can be quite a lot, several thousand). For any such row, and for certain groups, (in the example 0, 2, and 3) select

I have a query, which does the above, but it is tediously slow (includes two subselects for any group).

Regards,

Jochen

P.S: Is c.d.o.misc the right group for this type of questions? Received on Wed Sep 22 2004 - 03:01:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US