SELECT 1 AS `status`, `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `cat_index`.`position` AS `cat_index_position`, `e`.`entity_id`, `e`.`is_closeout`, `e`.`is_closeout_value`, `e`.`qty_arriving_soon`, `_stock_item_table`.`is_in_stock`, `_stock_item_table`.`manage_stock`, `_stock_item_table`.`min_sale_qty`, `_stock_item_table`.`qty`, `_stock_item_table`.`use_config_manage_stock`, `e`.`name`, `e`.`short_description`, `e`.`sku`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`small_image`, `e`.`thumbnail`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`url_key`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`tax_class_id`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`giftcard_amounts`, `e`.`allow_open_amount`, `e`.`open_amount_min`, `e`.`open_amount_max`, `e`.`pricing_model`, `e`.`case_pk_qty`, `e`.`inner_pk_qty`, `e`.`cube_inches`, `e`.`is_pallet`, `e`.`is_pallet_value`, `e`.`is_pdq`, `e`.`is_pdq_value`, `e`.`is_closeout`, `e`.`is_closeout_value`, `e`.`special_options`, `e`.`qty_arriving_soon`, `e`.`is_shippinglabel_product`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `e`.`starcrest_item`, `m`.`status` AS `limit_status`, `m`.`datetime_start`, `m`.`datetime_to`, `m`.`deal_price`, `m`.`deal_qty`, `m`.`datetime_from`, `m`.`position`, `m`.`minimum_order_quantity`, `e`.`small_image`, (CASE WHEN ((e.small_image = '') OR (e.small_image IS NULL) OR (e.small_image = 'no_selection')) THEN 0 ELSE 1 END) AS `has_image`, `_inventory_table`.`is_in_stock`, `_inventory_table`.`manage_stock`, `_inventory_table`.`min_sale_qty`, (CASE WHEN (_inventory_table.qty >= _inventory_table.min_sale_qty AND ((_inventory_table.use_config_manage_stock = 1) AND (_inventory_table.is_in_stock = 1)) OR ((_inventory_table.use_config_manage_stock = 0) AND (1 - _inventory_table.manage_stock + _inventory_table.is_in_stock +(_inventory_table.qty - _inventory_table.min_sale_qty) >= 1))
) THEN 1 ELSE 0 END) AS `on_top` FROM `catalog_product_flat_1` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '1010' AND cat_index.is_parent=1
LEFT JOIN `cataloginventory_stock_item` AS `_stock_item_table` ON _stock_item_table.product_id = e.entity_id
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 1
LEFT JOIN `multipledeals` AS `m` ON m.product_id=e.entity_id
LEFT JOIN `cataloginventory_stock_item` AS `_inventory_table` ON _inventory_table.product_id = e.entity_id WHERE (
(e.is_closeout is null or e.is_closeout = 0)
OR
e.qty_arriving_soon = 1
OR
(_stock_item_table.is_in_stock = 1 and _stock_item_table.qty >= _stock_item_table.min_sale_qty)
) AND (((e.starcrest_item IS NULL) OR (e.starcrest_item = 0))) ORDER BY `on_top` DESC, `has_image` DESC, `cat_index_position` asc LIMIT 24
Sort By
SortBy
Sort By
Price ASC
Price DESC
Quantity DESC
On Special
Item # ASC
Item # DESC
Position ASC
Position DESC
Showing 1-24 of 88
Notify Me When New Products Arrive In This Category
*You Will Be Notified When New Products Are Added To This Category.
