query

[shelarru]

3 tables and their structure:
1)Activities
Activityid Activitystatus
A300 C
A301 C
A302 C
2)Activityxtask
Activityid Taskid
A300 o11
A301 o11
A302 o11
3)Task
Taskid Taskstatus
o11 O
o12 C


every Task will have more than one activity.an activity will be closed by the activitystatus as 'C'(closed).if all activities are closed with activitystatus as 'C' then Task.taskstatus should become as 'C'.
But,there are some records in database,even all the Activities are closed for a perticular task,still some records are showing 'O' in Task.Taskstatus. i need to fetch all those records .
in the above eg o11 Task contains 3 Activities A300,A301,A302 whose Activitystatus is showing 'C'..but still Task.taskstatus showing 'O'.
pls tell me how to retreive all those records whose Activites are closed and still the task status is showing 'O'.

iam writng this query..

select a.bolid,axt.orderid
from Activities A,Activityxtask axt
where a.activityid=axt.activityid
and A.Activitystatus='E'
and exists (select 1 from Task T where axt.taskid=T.Taskid and T.taskstatus='A')


but this is returning the records in this sceanrio..
1)Activities
Activityid Activitystatus
A300 C
A301 O
A302 O
2)Activityxtask
Activityid Taskid
A300 o11
A301 o11
A302 o11
3)Task
Taskid Taskstatus
o11 O


i.e even one of the Activitues of one task is showing as C and Task.taskstatus is showing as 'O'.
i want all the records where all Activites are showing as 'C' and stil the corresponfing Task status is showing as 'O'..pls help me wat changes need to do this query..
thanks for help,

[tamilselvan]

When you post a question, you should also include "create table ..." statement as well as some sample data using "insert into table values (...)" statement.
That will make easy for others to reply ASAP.

Tamil