How to export products with tier prices directly via database using SQL?

Question

I want to export these data from my Magento 2.4:

• Status
• Product Name
• SKU
• Price
• Advanced Pricing
• Quantity

How could I do it via SQL?

Answer 1

With this script you might have almost all the information, just the stock qty is missing, but with the other script, you might have the stock too.

Price

SELECT `e`.*, `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`
FROM `catalog_product_entity` AS `e` 
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 = 0;

Stock

Filtered by only simple products.

SELECT sku,qty FROM cataloginventory_stock_item stock INNER JOIN catalog_product_entity e ON e.entity_id=stock.product_id AND e.type_id='simple';