How to get custom attribute value with product id of enable product with SQL query in Magento 2?

Question

How to get custom attribute value with product id of enabled product with SQL query in Magento 2?

I am using

SELECT entity_id,value FROM catalog_product_entity_varchar WHERE attribute_id = 150

But it gives me all data I want these data only to enable products.

Any help is appreciated.

Answer 1

try this code:

select
 A.entity_id,A.value
from
 catalog_product_entity_varchar A
inner join catalog_product_entity_int B
 on A.entity_id = B.entity_id
where
 B.attribute_id = 97 and B.value = 2 and A.attribute_id=73

in catalog_product_entity_int you have information about status (in my case it is attribute with id = 97). value == 2 means product is disabled and value == 1 means product is enabled. Above code will return disabled products with attribute 73 value.

• Prits: Thanks for your response I have used select A.entity_id,A.value from catalog_product_entity_varchar A inner join catalog_product_entity_int B on A.entity_id = B.entity_id where B.attribute_id = 97 and B.value = 1 and A.attribute_id=150 as 150 is my attribute id but it returns me enabled and disabled both on changeing value to 2 it returns me correct result Why it is return both enabled and disabled on value = 1
• Prits: It works with adding store_id value in query thanks for that. Will you please help me with this question magento.stackexchange.com/questions/323363/…
• SebastianT: Glat to hear it works for you! I will look at this after my work :D But not sure what is wrong, never did filtering by custom value...
• Prits: okk please look if you have time i have struck in this around 2 days but not getting any solution and again thanks for the help.