select ProductID , GroupID,PreOrder,0 as LC,(select top 1 TypeID from mss_Types where TypeID in (select typeid from mss_types where [sign]='DESIGNERS') and TypeID in (select TypeID from mss_Index where ProductID=cache_products.ProductID)) AS DesignerID from cache_Products where (publishdate < getdate() or publishdate is null) and 1=1 and exists (select top 1 1 from mss_index where typeid=5 and productid=cache_Products.productid and cache_products.GroupID <> 5433) and exists (select top 1 1 from mss_index where typeid=5 and productid=cache_Products.productid) order by PublishDate desc
Featuring detachable sleeves with zip and snap detachment, front zip and snap closure, adjustable high-neck hood with drawstring, Drawstring hem and flap and zip front pockets. Dense waterproof nylon shell is fully lined. White enamel metal snaps and contrast lining style this coat apart. Available in Black and Green.