SELECT	D.ProjectId,
        D.DrawingId,
        D.InternalDrawingNumber,
        (SELECT TOP 1 DrawingRevisionNumber FROM DrawingRevision WHERE DrawingId = D.DrawingId) AS 'DrawingRevisionNumber',
        (SELECT TOP 1 Date FROM DrawingRevision WHERE DrawingId = D.DrawingId) AS 'DrawingRevisionDate',
        D.DrawingTypeId,
        DT.DrawingTypeName,
        D.ParentDrawingId,
        D.ParentRevisionNumber,
        (SELECT InternalDrawingNumber FROM Drawing P WHERE D.ParentDrawingId = P.DrawingId) AS 'ParentInternalDrawingNumber'
FROM    Drawing D,
        DrawingType DT
WHERE   D.DrawingTypeId = DT.DrawingTypeId