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
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)
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
HAVING sum(e.qoh+e.qc) > 0
ORDER BY max(b.attr1_order), max(b.attr1_entry_id)
"Together we can change the game. For Arsenal's pre-match jersey, adidas joined forces with Parley for the Oceans to create a design with purpose. This football jersey features yarn spun from plastic that's reclaimed on the shore before it reaches our oceans. It has a lightweight construction, with graphics that evoke motion. The back collar carries the message ""For the Oceans.""
Show your support
An embroidered club crest sits on the chest
Climalite wicks sweat to keep you dry in every condition
Good for the oceans
This product is created with yarn made in collaboration with Parley for the Oceans: Some of the yarn features Parley Ocean Plastic™ which is made from recycled waste, intercepted from beaches and coastal communities before it reaches the ocean"
Embroidered Arsenal crest
Sweat-wicking Climalite fabric
This jersey is made with recycled polyester to save resources and decrease emissions
Ribbed crewneck; "For the Oceans" on back neck tape