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,4)
GROUP BY b.attr1
ORDER BY max(b.attr1_order), max(b.attr1_entry_id)
Kids' Nike Jr. Magista Obra II (FG) Firm-Ground Football Boot
Amplify your playmaking in Kids' Nike Jr. Magista Obra II (FG) Firm-Ground Football Boot. The Dynamic Fit collar integrates the foot and lower leg while a 3-D texture on key areas of the boot provides targeted friction for a precise touch. The forefoot pivot zone has circular traction for quick turns, and notched heel studs add braking power.
Shaped Dynamic Fit collar links the lower leg and foot without impeding motion.
3-D texture is amplified on the high-use areas of the foot.
Rotational zone in the forefoot is ideal for quick turns and agile playmaking.
Asymmetrical lacing system expands the ball control area.
Interior heel counter cups the foot for cushioning and a locked-down fit.
High-performance, molded EVA sockliner helps to reduce stud pressure and enhance comfort.