SELECT o.options_id AS oid, o.model, o.description, o.us_out_of_stock AS stock,
p.name, p.category, p.sh_desc, p.products_id AS pid, lower(p.title) AS title,
us_ret AS price, us_ret_s AS special,
DATE_FORMAT(c.ETA,'%b. %e/%y') AS ETA
FROM products p, options o, prices_us c, categories cat
WHERE o.product_id = p.products_id AND full_display = 'yes' AND lower(p.name) REGEXP '^[a-c]'
AND p.us_active='yes' AND o.us_active = 'yes'
AND c.prices_us_id = o.options_id AND cat.category = p.category
ORDER BY cat.order_val, p.category, p.display_order, p.name, o.display_order
