Back to Articles

PrestaShop SQL Manager: 15 useful queries worth saving

Catalogue audit & product data

SQL

// active products with current stock

SELECT

p.id_product,

p.reference,

sa.quantity

FROM ps_product p

JOIN ps_stock_available sa

ON sa.id_product = p.id_product

WHERE p.active = 1;

SQL Manager

Intermediate

08 Aug 2026 PrestaHacks 7 min read

Not every catalogue check in PrestaShop needs an export, a module or a custom script.

For many day-to-day jobs, a well-targeted SELECT is enough to find incomplete product data, duplicate references, zero stock, combinations without SKUs or records that need review before an import, export or integration.

Here are 15 practical SQL queries worth keeping in your toolbox.

Note

The examples use database prefix ps_, id_lang = 1 and id_shop = 1. Adapt them to the real values of your shop.

Warning

Every example below is a SELECT. Do not run UPDATE or DELETE against production data without a verified backup and rollback plan.

Where to run them

Use PrestaShop SQL Manager or a database client such as phpMyAdmin or Adminer.

1. All active products

SQL

SELECT
    p.id_product,
    p.reference,
    pl.name,
    p.price
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
WHERE p.active = 1
ORDER BY p.id_product DESC;

Returns Product ID, reference/SKU, product name and base price. It is a useful starting point for exports and catalogue audits.

2. Active products without a reference

SQL

SELECT
    p.id_product,
    pl.name,
    p.reference
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
WHERE p.active = 1
  AND (p.reference IS NULL OR TRIM(p.reference) = '')
ORDER BY pl.name;

A reliable reference becomes especially important when PrestaShop is connected to ERP systems, supplier feeds, marketplaces or spreadsheets.

3. Duplicate product references

SQL

SELECT
    reference,
    COUNT(*) AS total_products
FROM ps_product
WHERE reference IS NOT NULL
  AND TRIM(reference) <> ''
GROUP BY reference
HAVING COUNT(*) > 1
ORDER BY total_products DESC, reference;

Tip

Run this before integrations that use SKU/reference as an external identifier.

4. Products without EAN-13

SQL

SELECT
    p.id_product,
    p.reference,
    pl.name,
    p.ean13
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
WHERE p.active = 1
  AND (p.ean13 IS NULL OR TRIM(p.ean13) = '')
ORDER BY pl.name;

A missing EAN is not automatically an error. Treat this as a data-quality report.

5. Products without images

SQL

SELECT
    p.id_product,
    p.reference,
    pl.name
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
LEFT JOIN ps_image i
    ON i.id_product = p.id_product
WHERE p.active = 1
GROUP BY p.id_product, p.reference, pl.name
HAVING COUNT(i.id_image) = 0
ORDER BY pl.name;

Useful before a catalogue launch, marketplace feed, migration or visual QA pass.

6. Products with very few category associations

SQL

SELECT
    p.id_product,
    p.reference,
    pl.name,
    COUNT(cp.id_category) AS category_count
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
LEFT JOIN ps_category_product cp
    ON cp.id_product = p.id_product
WHERE p.active = 1
GROUP BY p.id_product, p.reference, pl.name
HAVING COUNT(cp.id_category) <= 1
ORDER BY category_count, pl.name;

Interpret the result according to your own category architecture.

7. Simple products with zero stock

SQL

SELECT
    p.id_product,
    p.reference,
    pl.name,
    sa.quantity
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
INNER JOIN ps_stock_available sa
    ON sa.id_product = p.id_product
    AND sa.id_product_attribute = 0
WHERE p.active = 1
  AND sa.quantity = 0
ORDER BY pl.name;

Available stock is read from ps_stock_available.

8. Products with negative stock

SQL

SELECT
    p.id_product,
    p.reference,
    pl.name,
    sa.quantity
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
INNER JOIN ps_stock_available sa
    ON sa.id_product = p.id_product
    AND sa.id_product_attribute = 0
WHERE sa.quantity < 0
ORDER BY sa.quantity ASC;

Useful for stock audits, overselling investigation and ERP sync checks.

9. Combinations without a reference

SQL

SELECT
    pa.id_product_attribute,
    pa.id_product,
    pl.name AS product_name,
    pa.reference
FROM ps_product_attribute pa
INNER JOIN ps_product_lang pl
    ON pl.id_product = pa.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
WHERE pa.reference IS NULL
   OR TRIM(pa.reference) = ''
ORDER BY pl.name, pa.id_product_attribute;

In catalogues with variants, checking only the parent ps_product.reference is not enough.

10. Combinations with zero or negative stock

SQL

SELECT
    p.id_product,
    pl.name AS product_name,
    pa.id_product_attribute,
    pa.reference,
    sa.quantity
FROM ps_product_attribute pa
INNER JOIN ps_product p
    ON p.id_product = pa.id_product
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
INNER JOIN ps_stock_available sa
    ON sa.id_product = pa.id_product
    AND sa.id_product_attribute = pa.id_product_attribute
WHERE sa.quantity <= 0
ORDER BY sa.quantity ASC, pl.name;

11. Products without a description

SQL

SELECT
    p.id_product,
    p.reference,
    pl.name
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
WHERE p.active = 1
  AND (pl.description IS NULL OR TRIM(pl.description) = '')
ORDER BY pl.name;

12. Products without a meta title

SQL

SELECT
    p.id_product,
    p.reference,
    pl.name,
    pl.meta_title
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
WHERE p.active = 1
  AND (pl.meta_title IS NULL OR TRIM(pl.meta_title) = '')
ORDER BY pl.name;

13. Base price and wholesale price

SQL

SELECT
    p.id_product,
    p.reference,
    pl.name,
    p.wholesale_price,
    p.price,
    (p.price - p.wholesale_price) AS gross_difference
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
WHERE p.active = 1
ORDER BY gross_difference ASC;

Warning

gross_difference is not a real commercial margin. It does not account for taxes, discounts, specific prices or other business rules.

14. Products with specific prices

SQL

SELECT
    p.id_product,
    p.reference,
    pl.name,
    COUNT(sp.id_specific_price) AS specific_price_rules
FROM ps_product p
INNER JOIN ps_product_lang pl
    ON pl.id_product = p.id_product
    AND pl.id_lang = 1
    AND pl.id_shop = 1
INNER JOIN ps_specific_price sp
    ON sp.id_product = p.id_product
GROUP BY p.id_product, p.reference, pl.name
ORDER BY specific_price_rules DESC, pl.name;

15. Mini catalogue health check

SQL

SELECT
    COUNT(*) AS total_active_products,
    SUM(CASE WHEN reference IS NULL OR TRIM(reference) = '' THEN 1 ELSE 0 END) AS missing_reference,
    SUM(CASE WHEN ean13 IS NULL OR TRIM(ean13) = '' THEN 1 ELSE 0 END) AS missing_ean
FROM ps_product
WHERE active = 1;

The query is only the start

SQL can become a fast workflow for:

Audit → Find → Export → Transform → Verify

Official references

https://devdocs.prestashop-project.org/9/faq/stock/
https://devdocs.prestashop-project.org/9/webservice/resources/stock_availables/

Next step

Turn these checks into reusable datasets with the SQL joins catalogue-reporting guide. If pricing anomalies are the focus, first review how Specific Prices change the effective price.