February 2, 2009 at 5:40 am
I have two tables Project_CreateMainTask as parent table
and Project_CreateTask as child table
Tables Project_CreateMainTask contains Columns ID,ProjectId,TaskName,StartDate,EndDateDescription
AND Table Project_CreateTask contain columns
ID,MainTaskId,ProjectId,TaskName,StartDate,EndDateDescription
MainTaskId in Project_CreateTask is equal to ID in Project_CreateMainTask
Now I want to write the Query in such way that it will give me the result as
It will select one record from Project_CreateMainTask
followed by all the records from Project_CreateTask which has MainTaskId equal to ID of above record
then again second from Project_CreateMainTask followed by
records from Project_CreateTask
how to do this?
Is it possible?
February 2, 2009 at 5:44 am
Exactly as you would find on an order or an invoice?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 2, 2009 at 6:30 am
...Not Enough Information, so I can only guess what you required. And my guess is that you require an INNER JOIN...
SELECT*
FROMdbo.Project_CreateMainTask MT
INNER JOIN dbo.Project_CreateTask T ON MT.ID = T.MainTaskId
To know more on how to post questions in right way, please read the following article[/url]
--Ramesh
February 2, 2009 at 9:27 pm
Hello guy's
I think you havent got my problem.......
I m providing an example
I have Table Project_CreateMainTask
ID ProjID UserId UserType Task StartDate EndDate
1 2 1admi Archictural Draing 9/8/2008 2/1/2009
2 2 1admin Build 10/12/2008 2/21/2009
And I have Table Project_CreateTask
ID MainTaskId UserId Task startDate EndDate
211 Task1 1/17/2008 1/22/2008
411 New Task 1/22/2008 2/7/2008
1511 Create Task 1/21/2008 12/31/2008
1621 Wiring 1/15/2008 2/29/2008
1711 taskCfa 1/22/2008 1/31/2008
1821 Plumbing 1/16/2008 2/21/2008
2011 a 1/19/2009 2/7/2009
2421 gdhf 12/28/2008 2/7/2009
Now I have to write the select query in such a way that
Resultset should give me output such a way that
It should select first row from Project_CreateMainTask
such as
1 2 1admi Archictural Draing 9/8/2008 2/1/2009
followed that all the rows from Project_CreateTask which has MainTaskId as Id of above Row
such as
211 Task1 1/17/2008 1/22/2008
411 New Task 1/22/2008 2/7/2008
1511 Create Task 1/21/2008 12/31/2008
Resultset will actually look like
ID ProjId Task StartDate EndDate
1 1 Archictural Draing 9/8/2008 2/1/2009
2 1 Task1 1/17/2008 1/22/2008
4 1 New Task 1/22/2008 2/7/2008
15 1 Create Task 1/21/2008 12/31/2008
17 1 taskCfa 1/22/2008 1/31/2008
20 1 a 1/19/2009 2/7/2009
2 1 Build 10/12/2008 2/21/2009
16 1 Wiring 1/15/2008 2/29/2008
18 1 Plumbing 1/16/2008 2/21/2008
24 1 gdhf 12/28/2008 2/7/2009
also i have to include one more Column which will tell me which is main task and which is sub task........
please help
February 3, 2009 at 2:41 am
sar_kan25 (2/2/2009)
Hello guy'sI think you havent got my problem.......
I m providing an example
...snip...
please help
Aha!
Exactly as you would find on an order or an invoice?
Can you please set up some sample data in the form of inserts - if you're not sure how to do this, please refer to the link below.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 3, 2009 at 2:50 am
I bet you've not read the article in my previous post, if you really wants us to help you, then in turn you would also need to help us by providing enough information in the appropriate way. Keep it in mind & spare some time reading the article...
But for now, here is the solution to your problem
--Method 1 (SQL 2005+)
;WITH ProjectCTE
AS
(
SELECTProjID AS ProjectID, ID AS MainTaskID, NULL AS SubTaskID, Task, StartDate, EndDate,
0 AS IsMainTask
FROMdbo.Project_CreateMainTask
UNION ALL
SELECTC.ProjID, C.MainTaskID, T.ID AS SubTaskID, T.Task, T.StartDate, T.EndDate, 1 AS IsMainTask
FROMProjectCTE C
INNER JOIN dbo.Project_CreateTask T ON C.MainTaskID = T.MainTaskId
)
SELECTCOALESCE( SubTaskID, MainTaskID ) AS ID,
ProjectID, Task, StartDate, EndDate
FROMProjectCTE
ORDER BY ProjectID, MainTaskID, SubTaskID
--Method 2 (SQL 2000+)
SELECTProjID AS ProjectID, ID AS MainTaskID, NULL AS SubTaskID, Task, StartDate, EndDate,
0 AS IsMainTask
FROMdbo.Project_CreateMainTask
UNION ALL
SELECTC.ProjID, MT.ID AS MainTaskID, T.ID AS SubTaskID, T.Task, T.StartDate, T.EndDate,
1 AS IsMainTask
FROMProject_CreateTask T
INNER JOIN dbo.Project_CreateMainTask MT ON T.MainTaskID = MT.ID
ORDER BY ProjectID, MainTaskID, SubTaskID
--Ramesh
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply