SELECT distinct (b.attr1) as origAttr1, max(CASE WHEN b.attr1_alias IS NULL OR RTRIM(LTRIM(b.attr1_alias)) = '' THEN b.attr1 ELSE b.attr1_alias END) as attr1, max(a.sku_id) as sku_id, max(b.attr1_entry_id) as attr1_entry_id, sum(e.qoh+e.qc) as qoh, max(b.attr1_order) as sort_order, max(c.attr1_hex) as hex,max(d.attr1_fam) as fam,max(z.non_invt) as non_invt
FROM tb_skus a
LEFT JOIN tb_attr1_entries b WITH (NOLOCK) ON (b.attr1_entry_id = a.attr1_entry_id)
LEFT JOIN tb_sku_buckets e WITH (NOLOCK) ON (e.sku_id = a.sku_id)
LEFT JOIN tb_attr1_hexes c WITH (NOLOCK) ON (b.attr1=c.attr1)
LEFT JOIN tb_attr1_families d WITH (NOLOCK) ON (c.attr1_fam_id = d.attr1_fam_id)
LEFT JOIN tb_styles z on (z.style_id = a.style_id)
WHERE (a.sell_web not in ('N','NO') or (a.sell_web is null)) and a.style_id=?
AND e.store_id in(1,2,3,4)
GROUP BY b.attr1
ORDER BY max(b.attr1_order), max(b.attr1_entry_id)
Built for athletes, the Stadium II Backpack is big enough to fit everything for practice or an away game and loaded with function to get you through your day. Updated design allows you to zip away your regulation size soccer ball into the main pocket or wear on the outside of the bag in an expandable mesh pocket. The bottom of the bag is made of HYDROSHIELD water-resistant material to keep your stuff safe from the field or gym floor. There is a front zippered pocket to keep your dirty cleats or sneakers separated, and a tricot-lined soft pocket for valuables. Lifetime warranty - built to last. Zippered bottom ball pocket that can hold a size 5 soccer ball inside or be worn on the outside in a mesh pocket. Dual water bottle pockets and a zippered tricot lined media pocket. UltraRide padded