Re: Help with a complex query

From: lge <elkinsl_at_flash.net>
Date: 1997/11/22
Message-ID: <34763b39.81510478@news.flash.net>#1/1

Casper,

You can get by the cross product problem of the two 1:M tables by using in-line views. Here is an example:

SQL> Select A.Account_Id,

  2         Nvl(B.Budget,0),
  3         Nvl(C.Actuals,0)
  4  From   Accounts A,
  5         (Select Account_Id, Sum(Amount) Budget
  6          From   Budgets
  7          Group By Account_Id) B,
  8         (Select Account_Id, Sum(Amount) Actuals
  9          From   Actuals
 10          Group By Account_Id) C

 11 Where A.Account_Id = B.Account_Id (+)  12 And A.Account_Id = C.Account_Id (+)  13 Group By A.Account_Id, B.Budget, C.Actuals  14 /

ACCOUNT_ID NVL(B.BUDGET,0) NVL(C.ACTUALS,0)

---------- --------------- ----------------
         1             300             1100
         2             700             1500
         3               0                0

Depending on the data and your needs, you might not be worried about the outer joins, and, the use of the nvl function. The example doesn't provide cases of budgets w/o actuals and vice versa; but, the code SQL should still account for that. The query above is a based on data in the following tables:

SQL> select * from accounts;

ACCOUNT_ID

---

         1
         2
         3

SQL> select * from budgets;

ACCOUNT_ID AMOUNT
---------- ---------

         1       100
         1       200
         2       300
         2       400

SQL> select * from actuals;

ACCOUNT_ID AMOUNT
---------- ---------

         1       500
         1       600
         2       700
         2       800

Hope this gets you on your way. By the way, the availability of in-line views did not come about "unofficially" until 7.1, then officially until 7.2. Since you didn't state your database version, I though I better mention it.

Later,

Larry G. Elkins
elkinsl_at_flash.net

Casper Thrane <ct_at_benau.dk> wrote:

>Hi!
>
>I have a big problem with a query. The situation is this.
>I have an acount table with a unique acountno. for each row. To this
>table I have two detail tables, one with usage and one with budget.
>
>For each acount there can be several rows for both usage and budget. I
>would like to get a total for both the usage and the budget for a
>specific acount in one query, how can I accomplish this?
>--
>Casper Thrane
>Systemdeveloper
>Benau A/S
Received on Sat Nov 22 1997 - 00:00:00 CST