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
ORDER BY max(b.attr1_order), max(b.attr1_entry_id)
"Sculpted for speed, these firm ground football boots are stripped down to the essentials. A low-cut collar, moulded heel and four-way-stretch tongue secure a lockdown fit. The upper is made of thin mesh for a minimal feel. The outsole is perforated to cut weight, leaving you free to fly.
Superthin Speedmesh upper delivers minimal feel for blink-quick speed and a superb, direct touch
Low-cut Clawcollar locks your foot into the boot for game-changing stability
Standout 3D moulded heel offers a secure fit and cushioned zones for optimum ankle movement
Ultralight Speedframe outsole perforated for pure acceleration
Stability on firm ground
Arrowhead forefoot studs combined with round heel studs enable quick starts and stops on firm ground"