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)
The Joma Aguila Gol FG Soccer Shoes are manufactured out of cutting-edge materials and are designed to provide comfort and performance on gameday. Featuring a classic look with modern construction these shoes will provide the soft touch and superior traction you need.
Breathable leather upper designed to prevent water absorption.
Water Resistant technology.
Joma's 360º system designed to improve the joint between sole and upper, favoring the natural distribution of weight on foot on the foot.
M COUNTER technology at the heel providing better protection and improved comfort with an ergonomic shape.
Pre-shaped EVA insole that molds to the shape of the foot for added cushioning.
Semi-rigid PVC midsole to firmly retain the structure of the sole.