Something

SELECT 
w.ID as ID, 

CASE WHEN b.CompID IS NULL AND p.MaxBOMlevel = 1 THEN 0
WHEN p.MaxBOMlevel > 1 THEN 0
ELSE 1 END as Depth,
w.Name,
w.LayoutFontID as LayoutFontID,
w.Description,
w.SortName, 
w.Status as Status

FROM G0_ProductWrapperSF w
LEFT JOIN
G0_Products p on (p.SFWrapperID = w.ID)
LEFT JOIN
G0_BOM_FullyAggregated b on (b.CompID = p.ID and b.CompTypeID = 2)