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. The mesh upper has a minimal feel, with a low-cut collar and sock-like fit that lock your foot into the boot. The outsole is lightweight, leaving you free to fly.
Low-cut silhouette with signature Clawcollar shape locks your foot into the boot for match-long stability and support
A minimal feel and more direct touch from a lightweight mesh upper engineered for high acceleration
Premium sock-like construction for adaptive support, superlight comfort and ease of entry
Lightweight TPU outsole offers the best balance between high speed and traction
Stability on firm ground
Arrowhead forefoot studs combined with round heel studs enable quick starts and stops on firm ground"