Select/Join within same table?

Transact-SQL
1

Hi, I have an SQL query that has stumped me.

I have 2 tables, Projects and Tasks.

Projects
project_id
original_proj_id

Tasks
project_id
task_id
finish_date

Each project has an original project ID with original tasks. What select query would produce the following output, with the original_finish_date coming from the the original project id task with the same task_id?

Output
proj_id
task_id
finish_date
original_finish_date

Example with data

2

This isn't too clear what you're trying to achieve. Would you provide some actual data in your worked example?

3

Ok I have added data

4

This does the job i think

DECLARE @Projects TABLE
(
	project_id INT,
	original_proj_id int
);

INSERT INTO @Projects
VALUES (101,NULL),(102,101);

DECLARE @Tasks TABLE
(
	project_id INT,
	task_id int,
	finish_date datetime
);

INSERT INTO @Tasks
VALUES (101,1,'20150910'),
(101,2,'20150911'),
(101,3,'20150912'),
(102,1,'20150910'),
(102,2,'20150912'),
(102,3,'20150914'),
(102,4,'20150915');

SELECT	T.project_id,
		T.task_id,
		T.finish_date,
		orig_finish_date = T1.finish_date
FROM	@Tasks AS T
INNER
JOIN	@Projects AS P
		ON	T.project_id = P.project_id
LEFT
JOIN	@Tasks AS T1
		ON	T1.project_id = P.original_proj_id
			AND T1.task_id = T.task_id;
1 Like
5

Thanks this works!