Exporting products from mysql with attributes from two tables

[avgear.shop]

Hello.

I would like to see which products in the shop have the parameter out_of_stock = Deny orders checked, but only for active products. 

I have an idea to export them from mysql database, but I need to work with two tables, active = 1 with ps_product and out_of_stock = 0 with ps_stock_available

How can I export to a single csv the products that meet both conditions? 

[WebDesk Solution]

Hello @lukaszwojcik

Please follow the steps below to export a CSV file with the behavior Deny Order and Status Active:

Step 1: Log in to your Prestashop store backend/admin.

Step 2: Go to Advanced Parameters > Database Menu from the Sidebar panel: https://prnt.sc/CDYq2idyBvmr 

Step 3: Click on Add new SQL query button and use the provided SQL query: https://prnt.sc/iy0LhWCj_xltand click on the Save button to save the query results.

SELECT SQL_CALC_FOUND_ROWS
    p.`id_product` AS `id_product`,
    p.`reference` AS `reference`,
    pl.`name` AS `name`,
    sa.`active` AS `active`,
    s.id_product_attribute AS `Attribute ID`,
    s.out_of_stock AS `Default Behaviour(Deny Order)`
FROM
    `ps_product` p
LEFT JOIN `ps_product_lang` pl ON
    (
        pl.`id_product` = p.`id_product` AND pl.`id_lang` = 1 AND pl.`id_shop` = 1
    )
JOIN `ps_product_shop` sa ON
    (
        p.`id_product` = sa.`id_product` AND sa.id_shop = 1
    )
JOIN `ps_stock_available` s ON
    (
        p.id_product = s.`id_product`
    )
LEFT JOIN `ps_shop` shop ON
    (
        shop.id_shop = 1
    )
WHERE
    (
        1 AND state = 1 AND p.active = 1 AND s.out_of_stock = 0
    )
ORDER BY
    `id_product` ASC

Step 4: Click on the download button to export the CSV file: https://prnt.sc/UbPKdg7jnUU0 

Hope this will work for you!!