Complex Query

[xloop]

I would really appreciate anyone's help with this as so far i can only return the expected results via about 5 queries and i would like to be able to do it in one.

I have 4 tables in a hierarchy patten;

tblClients
tblProjects (links to tblClients)
tblJob (links to tblProjects)
tblTask (links to tblJob)

So if i where to take a random ID from tblTask i could, by going through the heirachy find out the Client. So far so good.

I have another table 'tblPOs'
which has these columns (and a few others not mentioned)
POID
RaiseAgainstID
RaiseID
GTotal

RaiseAgainstID holds a number between 1 - 4 and this relates to either tblClients(1), ProjectID (2), tblJob.ID (3) and ID (4).

RaiseID holds the actual id which it relates to (depending on what RaiseAgainstID is).

So, if i had a RaiseAgainstID of 3 and a RaiseID of 50 then i would look in the tblJob table for ID 50.

Now to my final question, given the ClientID of 5 (respectivley) how do i find out the SUM of GTotal of all decendents relating to that ID, I.e All projects relating to that client then all jobs relating to that project etc etc...

Obviously you might be able to see how i can do this in 4 seperate queries, just do a straight inner join on RaiseID to the appropriate table. However, it becomes more difficult than that.

Thank you

 

[rt63]

select sum(GTotal)
from tblPOs
where (RaiseAgainstID = 1 and RaiseID = 5)
or (RaiseAgainstID = 2 and RaiseID in
(select ProjectID from tblProjects where ClientID = 5))
or (RaiseAgainstID = 3 and RaiseID in
(select JobID from tblJob , tblProjects
where tblJob.ProjectID = tblProjects.ProjectID
and tblProjects.ClientID = 5))
or (RaiseAgainstID = 4 and RaiseID in
(select TaskID from tblTask , tblJob , tblProjects
where tblTask.JobID = tblJob.JobID
and tblJob.ProjectID = tblProjects.ProjectID
and tblProjects.ClientID = 5)) RT