SELECT D.ProjectId, D.DrawingId, D.InternalDrawingNumber, (SELECT TOP 1 DrawingRevisionNumber FROM DrawingRevision WHERE DrawingId = D.DrawingId ORDER BY Date DESC) AS 'DrawingRevisionNumber', (SELECT TOP 1 Date FROM DrawingRevision WHERE DrawingId = D.DrawingId ORDER BY Date DESC) 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 DTWHERE D.DrawingTypeId = DT.DrawingTypeId