I am trying to create a SQL query that will display all the SKUs of a simple product that are related to a configurable product. I will add the configurable SKU via a form field that I want to query.
Magento 1.9.2
Magento – SQL Query to fetch all child SKUs by using the configurable SKU
ce-1.9.2.0magento-1.9productskusql
Related Solutions
The problem is that you use an implicit INNER JOIN and define the join conditions for attribute tables in WHERE clauses.
That means, if there is no value saved for one of the attributes with ID 41 and 57, you get no results.
Improved version with LEFT JOIN on the attribute tables and the entity table as main table:
SELECT DISTINCT
main_table.entity_id AS id,
at_path.`value` AS path,
at_name.`value` AS `NAME`,
main_table.`level`,
main_table.parent_id
FROM
catalog_category_entity main_table
JOIN
eav_entity_type ee
ON
main_table.entity_type_id = ee.entity_type_id AND ee.entity_model = 'catalog/category'
LEFT JOIN
catalog_category_entity_varchar at_path
ON
at_path.entity_id=main_table.entity_id AND at_path.attribute_id = 57
LEFT JOIN
catalog_category_entity_varchar at_name
ON
at_name.entity_id=main_table.entity_id AND at_name.attribute_id = 41
Now the column for attribute 57 (which is url_path) will show up as NULL in the result if there is no value.
Well you could get Magento to do all the work, I suppose. Why would you want to do that, you're just taking resource off your store? As to moving text files around via JSON - you've got to extract it, dump it somewhere, arrange security on the file location, consume the JSON string across the network, parse it into a database location for reporting and keep an eye on the job to make sure it's running reliably.
Or you could just use ODBC and talk directly to MySQL.
Actually in terms of the question, it's tricky because you have to understand all of Magentos little quirks, from your code I can see immediately that you're not accounting for configurable and simple items, so the danger here is that you're duplicating lines on the join, you need to ensure you're getting uniques before joining to sales. I'd build a product model query, build a stock model query and build a sales model and then relate them together on the other side of the fence, it makes life a lot easier. You could create a MySQL stored procedure to do this, but they're not very efficient because MySQL doesn't precompile.
Best Answer