Re: Query same table twice?
From: user (user_at_nowhere.com)
Date: 06/20/04
- Next message: Ross McKay: "Re: InterBase to SQL Server 2000 Conversion"
- Previous message: Seeker: "Re: SQL help needed"
- In reply to: Rob Hanes: "Query same table twice?"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 20 Jun 2004 13:39:24 +1000
I think that you can achieve that result with something like this:
select isnull(tblProjects.ParentProjID, tblProjects.ProjectID) as MasterID,
(select ProjectName from tblProjects where ProjectID =
isnull(tblProjects.ParentProjID, tblProjects.ProjectID)) as MasterName,
sum(Budget) as TotBudget,
sum(Actual) as TotActual
from tblProjects, tblFinancials
where tblProjects.ProjectID = tblFinancials.ProjectID
group by isnull(tblProjects.ParentProjID, tblProjects.ProjectID);
This groups records according to their "master project ID" where I've
assumed that a null ParentProjID means that is a master project record, then
aggregates them.
Doug
"Rob Hanes" <sqlstuck@dodgeit.com> wrote in message
news:a1124823.0406140741.74b8525c@posting.google.com...
> In the database I am querying we have two tables as follows:
>
> MAIN_TABLE
> ProjID | ParentProjID | Name
> ------------------------------------------
> 1001 | NULL | Cleethorpes Office
> 3234 | 1001 | Carpet Fitting
> 6642 | 1001 | Electrical Fit-out
> 4532 | 1001 | Plastering
>
> FINANCIALS_TABLE
> ProjID | Budget | Actual | Period
> ---------------------------------
> 1001 | 30,000 | NULL | 1
> 3234 | NULL | 3,000 | 1
> 3234 | NULL | 7,000 | 1
> 6642 | NULL | 2,000 | 1
> 6642 | NULL | 5,000 | 1
> 6642 | NULL | 3,000 | 1
> 4532 | NULL | 1,000 | 1
> 4532 | NULL | 2,000 | 1
> 4532 | NULL | 4,000 | 1
> 4532 | NULL | 2,000 | 1
>
> I would like to be able to write a report to query both of these
> tables and output one table like the following:
>
> BUDGET/ACTUAL FOR PERIOD 1
> ProjID | Name | Project Budget | Sub-project Actual SUM
> ---------------------------------------------------------------------
> 1001 | Cleethorpes Office | 30,000 | 29,000
>
> At the moment, I am able to extract this information, but only with
> two queries, due to having to query the same tables twice.
>
> Is there any way to do this with just one query?
>
> All help gratefully recieved!
>
> --
> Rob
- Next message: Ross McKay: "Re: InterBase to SQL Server 2000 Conversion"
- Previous message: Seeker: "Re: SQL help needed"
- In reply to: Rob Hanes: "Query same table twice?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
