join 3 tbl help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

join 3 tbl help!

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-11-16 : 02:04:58

Lets say,table name:RequestDetails

Requestid(Pk) ParentID OriginRequestID

96 NULL 96
97 96 96
98 96 97
108 96 97
241 96 97
104 96 98

this kind of parent and chid and subchild structure..

This sample data,If i passed the parameter as 97.

Output as
Requestid:
97
98
108
241
104
query below Please see

; WITH ReqUESTCTE(Requestid)
AS
(
SELECT
Requestid
FROM dbo.RequestDetails
WHERE
Requestid= 97

UNION ALL

SELECT
E.REQUESTID FROM dbo.RequestDetails AS E
JOIN ReqUESTCTE AS M
ON E.ParentID = M.RequestID
)

select * from ReqUESTCTE

Resultid is :97,108,241,104.after getting this we will have to find the Taskid for all these reQUESTID..
please see this Task tbl..after getting this taskid,we wil have check the TaskResourcedAssignedTbl of
Submitted Column(bit datatype) for all the Taksids,submitted column should be 1 means return 1 ,else return 0.

Table NAme:Task

Taskid(Pk) Requestid

11 96
12 96
13 96
16 98
17 98
24 104
72 97
91 97

TaskResourceAssignedTbl

TRAID(pk) Taskid submitted

15 11 0
16 11 0
17 12 0
18 12 0
23 16 0
24 16 0
25 16 0

any query please..

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-11-16 : 09:25:04

CAN ANYONE HELP ME OUT ON THIS.

AM I NOT CLEAR ABOUT MY REQ OVER HERE..pL REPLY

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-16 : 09:27:42

No you are unclear about what you want.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-11-16 : 10:40:18

Lets say,table name:RequestDetails

Requestid(Pk) ParentID OriginRequestID

96 NULL 96
97 96 96
98 96 97
108 96 97
241 96 97
104 96 98

Table NAme:Task

Taskid(Pk) Requestid

11 96
12 96
13 96
16 98
17 98
24 104
72 97
91 97

TaskResourceAssignedTbl

TRAID(pk) Taskid submitted

15 11 0
16 11 0
17 12 0
18 12 0
23 16 0
24 16 0
25 16 0

These 3 tbl we have ok..

if i pass the parameter as 97.
first we have to get the requestids for 97,childs and sub childs..This case (97,108,241,104) ..ok.
after this we will have join the TASK TBL and get the Taskids for all the requetsids(97,108,241,104) and thebn we will have to compare the Taskresource AssignedTbl for this Taskids,submitted column should be 1 for all these taskid's ,then return 1 else if any one submitted is 0 or all 0,we will have to return to 0.

this is it..
any sample query please

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources