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)
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