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)
Give a new meaning to exceptional play in the Visaro 2.0 Control. A moccasin synthetic upper with molded foam offers a lightweight durable fit, improved ball control and enhanced strike zone. A Fresh foam EVA insole delivers exceptional cushioning for game long comfort. The New Balance Visaro 2.0 Control FG soccer cleat has a transparent TPU plate with nylon chassis that provides you with maximum speed, grip and performance on firm ground pitches. FEATURES: Full moccasin synthetic upper provides a seamless wrap underfoot on the lateral side, giving you all the extra support you could want Molded foam upper provides specific receiving support and strike zones to keep you in the game Fresh Foam EVA midsole offers enhanced padding for lasting comfort Pro-form last gives you a better feel with unmatched comfort Transparent TPU plate with a specially designed nylon chassis improved traction, acceleration and play