DROP VIEW DrawingVW
GO
CREATE VIEW DrawingVW 
SELECT	D.ProjectId, D.DrawingId, 
		D.InternalDrawingNumber, DT.DrawingTypeName, 
		DR.DrawingRevisionNumber, D.ModuleId, DR.Date, D.ParentDrawingId, 
		D.ParentRevisionNumber,
		(SELECT	InternalDrawingNumber
		 FROM	Drawing
		 WHERE DrawingId = ParentDrawingId)
FROM	Drawing D, DrawingRevision DR, DrawingType DT
WHERE	D.DrawingId = DR.DrawingId
AND		D.DrawingTypeId = DT.DrawingTypeId

-- TEST AREA::: trying to pull back values for drawings with parents only
SELECT	DrawingId, InternalDrawingNumber, ParentDrawingId
FROM	Drawing
WHERE	DrawingId = (Select ParentDrawingId
					FROM	Drawing
					WHERE	ParentDrawingId = ())