Hi folks,
I have a Projects , each project have many tasks now i want to display tasks replated to each project:
for example:
Project1-->task1
task2
task3
task4
Project2-->task4
task5
task6
.............................................projectN.....................
how to write query for this
i have 2 tables:
Project .......>columns are projectid
Task->columns are projectid, taskid
|
Select p.projectid, t.Taskid
From Project p
Left Join Task t --For the case of no assigned tasks
ON p.projectid = t.taskid
order by p.projectid, t.taskid
In this case you would not have to use a group by.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
Consider this example
Project
1
2
3
Task
1,A
1,B
2,C
I you want all tasks and their projects, you could write
SELECT ProjectID, TaskID
FROM Task
GROUP BY ProjectID, TaskID
ORDER BY ProjectID, TaskID
This would return
1,A
1,B
2,C
but would provide nothing about Project 3 because it has no tasks.
Given the structure of the table (no repeated tasks), you might even get away with
SELECT ProjectID, TaskID
FROM Task
ORDER BY ProjectID, TaskID
However, if you wanted all projects and to include their tasks, if available, then Jens is correct
Select p.projectid, t.Taskid
From Project p
Left Join Task t --For the case of no assigned tasks
ON p.projectid = t.taskid
order by p.projectid, t.taskid
This would return
1, A
1, B
2, C
3, NULL
The NULL on the last row exists because Project 3 is shown (from the Project table) but there is not equivalent task in the Task table.
The word LEFT from LEFT JOIN tells SQL to return all rows from the Project table and, if available, the row data from Tasks too. If there is no row in Task for a Project in Project, then NULL is placed in the task specific columns.
By dropping the word LEFT, it would limit the result to only those projects that had tasks (but the earlier examples are simpler).
If you want to replace NULL with something more useful, using ISNULL, you could write
SELECT p.projectid, ISNULL(t.Taskid, 'No associated tasks')
FROM Project p
LEFT JOIN Task t ON p.projectid = t.taskid
ORDER BY p.projectid, t.taskid
This would now return
1, A
1, B
2, C
3, No associated tasks
regards,
Niall.
