USE tempdb
go

---------------------------------------------------------------
--		Set up the structure & data...
---------------------------------------------------------------
CREATE TABLE dbo.org
	(
		unit_code		VARCHAR(10)	NOT NULL
		, parent_code	VARCHAR(10)	NULL
		, CONSTRAINT pk_org	PRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100)
	)
GO	

CREATE TABLE dbo.org_peeps
	(
		unit_code		VARCHAR(10)	NOT NULL
		, person_code	INT			NOT NULL
		, CONSTRAINT pk_org_peeps	PRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100)
		, CONSTRAINT fk_org_peeps_other_org_peeps	FOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code)
	)
GO	

CREATE VIEW dbo.org_peeps_parents
--WITH SCHEMABINDING
AS
	SELECT	  org.unit_code
			, org.parent_code
			, org_peeps.person_code
	FROM	dbo.org
	LEFT OUTER JOIN 
			dbo.org_peeps
	ON	org_peeps.unit_code		= org.unit_code
GO	

INSERT INTO dbo.org (unit_code, parent_code)
SELECT    'a', 'y'		UNION ALL
SELECT    'b', 'y'		UNION ALL
SELECT    'c', 'z'		UNION ALL
SELECT    'y', 'z'		UNION ALL
SELECT    'z', NULL

--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)
INSERT INTO dbo.org_peeps (unit_code, person_code)
SELECT    'a', 1	UNION ALL
SELECT    'c', 3

---------------------------------------------------------------
SELECT	'This works great....'
---------------------------------------------------------------
;WITH	materialised_paths
AS
	(
		SELECT	unit_code
				, parent_code
				, person_code
				, unit_path		= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
		FROM	dbo.org_peeps_parents
		WHERE	parent_code IS NULL
		UNION ALL
		SELECT	all_people.unit_code
				, all_people.parent_code
				, all_people.person_code
				, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
		FROM	dbo.org_peeps_parents AS all_people
		INNER JOIN
				materialised_paths AS mp 
		ON	mp.unit_code		= all_people.parent_code
	)

SELECT	parents.unit_code
		, children.person_code
		, children.unit_path
FROM	materialised_paths AS children
CROSS APPLY	--Correlated derived table - get the child records per unit
		(
			SELECT	unit_code
					, parent_code
			FROM	dbo.org		AS parents_sub
			WHERE	children.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
		) AS parents
WHERE	children.person_code IS NOT NULL
ORDER BY person_code
		, unit_code


--Add data to INTERMEDIATE levels of organisation
INSERT INTO dbo.org_peeps (unit_code, person_code)
SELECT    'y', 4	UNION ALL
SELECT    'y', 5	UNION ALL
SELECT    'y', 6

---------------------------------------------------------------
SELECT	'Since there is now data in the intermediate tables there are ' +
		'duplicate outputs....'
---------------------------------------------------------------
;WITH	materialised_paths
AS
	(
		SELECT	unit_code
				, parent_code
				, person_code
				, unit_path		= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
		FROM	dbo.org_peeps_parents
		WHERE	parent_code IS NULL
		UNION ALL
		SELECT	all_people.unit_code
				, all_people.parent_code
				, all_people.person_code
				, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
		FROM	dbo.org_peeps_parents AS all_people
		INNER JOIN
				materialised_paths AS mp 
		ON	mp.unit_code		= all_people.parent_code
	)
SELECT	parents.unit_code
		, children.person_code
		, children.unit_path
FROM	materialised_paths AS children
CROSS APPLY	--Correlated derived table - get the child records per unit
		(
			SELECT	unit_code
					, parent_code
			FROM	dbo.org		AS parents_sub
			WHERE	children.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
		) AS parents
WHERE	children.person_code IS NOT NULL
ORDER BY person_code
		, unit_code

---------------------------------------------------------------
SELECT	'This is the output I want - but I can''t simply apply distinct - the volumes of data preclude this'
---------------------------------------------------------------
;WITH	materialised_paths
AS
	(
		SELECT	unit_code
				, parent_code
				, person_code
				, unit_path		= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
		FROM	dbo.org_peeps_parents
		WHERE	parent_code IS NULL
		UNION ALL
		SELECT	all_people.unit_code
				, all_people.parent_code
				, all_people.person_code
				, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
		FROM	dbo.org_peeps_parents AS all_people
		INNER JOIN
				materialised_paths AS mp 
		ON	mp.unit_code		= all_people.parent_code
	)
SELECT	DISTINCT 
		parents.unit_code
		, children.person_code
		, children.unit_path
FROM	materialised_paths AS children
CROSS APPLY	--Correlated derived table - get the child records per unit
		(
			SELECT	unit_code
					, parent_code
			FROM	dbo.org		AS parents_sub
			WHERE	children.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
		) AS parents
WHERE	children.person_code IS NOT NULL
ORDER BY person_code
		, unit_code


--Clean up
IF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN
	DROP VIEW dbo.org_peeps_parents
END

IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN
	DROP TABLE dbo.org_peeps
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN
	DROP TABLE dbo.org
END

;WITH	materialised_paths
AS
	(
		SELECT	unit_code
				, parent_code
				, unit_path		= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
		FROM	dbo.org
		WHERE	parent_code IS NULL
		UNION ALL
		SELECT	all_orgs.unit_code
				, all_orgs.parent_code
				, mp.unit_path + CAST(all_orgs.unit_code AS VARCHAR(MAX)) + '/'
		FROM	dbo.org AS all_orgs
		INNER JOIN
				materialised_paths AS mp 
		ON	mp.unit_code		= all_orgs.parent_code
	)
SELECT	all_orgs.unit_code
		, org_peeps.person_code
		, all_orgs.unit_path
		, org_peeps.unit_code
FROM	dbo.org_peeps
INNER JOIN 
		(
			SELECT	org.unit_code
					, mp.unit_path
			FROM	materialised_paths AS mp
			CROSS APPLY
					(
						SELECT	unit_code
								, parent_code
						FROM	dbo.org
						WHERE	mp.unit_path LIKE '%/' + org.unit_code + '/%'
					) AS org
		) AS all_orgs
ON	all_orgs.unit_path		LIKE '%/' + org_peeps.unit_code + '/'
ORDER BY person_code
		, all_orgs.unit_code