HWA2_P6_2.sql

/* -------------------------------------------------------------------------
   Yu Cheng
   ICS 321 Assignment 2
   October 23, 2008

   PART 6.2
   Provide the script that will create a stored procedure which will select
   an organization entity and show all subordinate organization entity(ies)
   at the next level down. Use the attribute names 'Parent Org', and
   'Subordinate Org'.  Name the stored procedure OrganizationStructure.
   ------------------------------------------------------------------------- */

USE DB9975
GO

CREATE PROCEDURE OrganizationStructure AS
	SELECT
		PARENT.Title AS [Parent Org],
		CHILD.Title  AS [Subordinate Org]

	FROM ORGANIZATION AS PARENT

	JOIN ORGANIZATION AS CHILD
		ON PARENT.Id = CHILD.ParentId

	ORDER BY [Parent Org], [Subordinate Org]
GO

EXECUTE OrganizationStructure
GO