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)
Nike Phantom 3 Academy (FG) Men's Firm-Ground Football Boot
DEADLY FINISHING. ULTIMATE FIT.
Built with a mesh upper, integrated textile tongue and dynamic cords that secure your foot, Nike Phantom 3 Academy (FG) Men's Firm-Ground Football Boot is coiled and ready to strike. Aggressive allover texture enhances touch while chevron and hexagon studs provide targeted traction for agility on the pitch.
Dynamic cords on the lateral side provide lockdown during quick cuts.
Aggressive texture provides cushioning and enhanced touch.
Integrated textile tongue hugs your foot.
Asymmetrical lacing system expands the ball control area and strike zone.
Chevron studs aid traction for acceleration, while hexagon studs help with rotational traction.