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)
Nike Jr. Zoom Mercurial Superfly 9 Academy TF
Little/Big Kids' Turf Soccer Shoes
You've got the skills, you've got the game. Now show them off with the Nike Jr. soccer shoes. Pull tabs and dual straps combine for an easy-on design that eliminates the need for laces and helps get you on the pitch fast. Plus, Zoom Air in the heel helps add extra bounce and comfort to turf fields, so you're ready for every practice, scrimmage and game.
Zoom, Zoom!
Snappy and responsive, Zoom Air cushioning helps provide a quick-off-the-ground sensation. Contain Your Speed
The speed cage inside is made of a thin but strong material that secures your foot to the plate without adding extra weight. All-Weather Touch
A molded, synthetic upper features a textured pattern for better ball control when dribbling, passing and shooting. Dig In, Take Off
Rubber outsole is designed for exceptional traction on turf surfaces.