Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Simple Stock Status Query
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Items that are out of stock

SELECT  a.skub.value as name FROM catalog_product_entity a
inner join catalog_product_entity_varchar b on a
.entity_id b.entity_id
left join cataloginventory_stock_status c on a
.entity_id c.product_id
where b
.attribute_id 96 and c.stock_status0

Some one asked for this query and here it is!

Enjoy

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Here is with disabled

SELECT  a.skub.value as name FROM catalog_product_entity a
inner join catalog_product_entity_varchar b on a
.entity_id b.entity_id
left join cataloginventory_stock_status c on a
.entity_id c.product_id
left join catalog_product_entity_int d on a
.entity_id d.entity_id
where 
(b.attribute_id 96) and (c.stock_status0) and (d.attribute_id 273)
and 
d.value 1
 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

With ETA

SELECT  a.skub.value as namee.value as ETA FROM catalog_product_entity a
inner join catalog_product_entity_varchar b on a
.entity_id b.entity_id
left join cataloginventory_stock_status c on a
.entity_id c.product_id
left join catalog_product_entity_int d on a
.entity_id d.entity_id
left join catalog_product_entity_varchar e on a
.entity_id e.entity_id
where 
(b.attribute_id 96) and (c.stock_status0) and (d.attribute_id 273)
and (
d.value 1) and (e.attribute_id 1066)
 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Ok, my join limited the query results, I have a solution here

Does anyone have a better one?

SELECT  a.skub.value as namee.value as ETA FROM catalog_product_entity a
left outer join catalog_product_entity_varchar b on a
.entity_id b.entity_id
inner join cataloginventory_stock_status c on a
.entity_id c.product_id
left outer join catalog_product_entity_int d on a
.entity_id d.entity_id
right outer join catalog_product_entity_varchar e on a
.entity_id e.entity_id
where 
(b.attribute_id 96) and (c.stock_status0) and (d.attribute_id 273)
and (
d.value 1) and (e.attribute_id 1066)
UNION
SELECT  a
.skub.value as name'n/a' as ETA FROM catalog_product_entity a
inner join catalog_product_entity_varchar b on a
.entity_id b.entity_id
left join cataloginventory_stock_status c on a
.entity_id c.product_id
left join catalog_product_entity_int d on a
.entity_id d.entity_id
where 
(b.attribute_id 96) and (c.stock_status0) and (d.attribute_id 273)
and 
d.value and a.entity_id not in
(select entity_id from catalog_product_entity_varchar where attribute_id 1066)
order by SKU
 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top