How to list unused attributes options by sql query

Question

I would like to list all attributes options that our products don't use. I prefer to use SQL to do that to learn how to work with it.

Right now I use this query to get the ID of my attributes

select * from eav_attribute where attribute_code = 'color_shoes';

After I list all the attributes options with this query.

select * 
from eav_attribute_option eao
join eav_attribute_option_value eaov on eao.option_id = eaov.option_id
where attribute_id = 2294
and eaov.store_id = 1;

It's here where I don't know to fetch all products based on the current list of attributes options.

What is the relation between EAV tables and Catalog products? right now in the 'catalog_product_entity' table, I have an ATTRIBUTE_ID column but there's no product with attribute_id = 2294.

Someone have hint where i need to look for?

Answer 1

I think you should review the EAV structure tutorials.

https://inchoo.net/magento/magentos-database-layout-and-its-eav-structure/

https://devdocs.magento.com/guides/m1x/magefordev/mage-for-dev-7.html

https://belvg.com/blog/eav-in-magento-2.html

Still, you required any help, let me know.

Hope this will help you.