# MySQL Statements

**URL:** <https://www.dolibarr.org/forum/t/mysql-statements/21415>\
**Category:** Using my Dolibarr\
**Tags:** Code, Guide, Reports, v14\
**Created:** [November 11, 2021, 8:32pm UTC](https://www.dolibarr.org/forum/t/mysql-statements/21415 "2021-11-11T20:32:14Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![aljawaid](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/aljawaid/32/388_2.png) [@aljawaid](https://www.dolibarr.org/forum/u/aljawaid)\
**Post date:** [November 11, 2021, 8:32pm UTC](https://www.dolibarr.org/forum/t/mysql-statements/21415/1 "2021-11-11T20:32:14Z")

</div>

Does anybody have any sql statements they have used for their business in Dolibarr?

I will share mine also for the greater good of the Dolibarr community.

If you have any to share, you are welcome to add a comment to this post using the general format below:

```auto
<title of report or what the mysql statement will produce when run>
<state dolibarr version>
<display mysql statement>

```

My statements are all read-only, so no data will be affected.

---

<div class="post-metadata">

**Author:** ![aljawaid](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/aljawaid/32/388_2.png) [@aljawaid](https://www.dolibarr.org/forum/u/aljawaid)\
**Post date:** [November 11, 2021, 8:36pm UTC](https://www.dolibarr.org/forum/t/mysql-statements/21415/2 "2021-11-11T20:36:04Z")

</div>

**Products Without Categories**  
`Dolibarr v14.0.2`

```auto
SELECT DISTINCT
    u.rowid AS "Category ID",
    u.label AS "Category Label",
    u.description AS "Category Description",
    p.ref AS "Product Code",
    p.label AS "Product Name",
    p.rowid AS "Product ID"
FROM
    llx_categorie AS u
JOIN llx_categorie_product AS cp
ON
    cp.fk_categorie = u.rowid
RIGHT JOIN llx_product AS p
ON
    p.rowid = cp.fk_product
WHERE
    u.label IS NULL AND `fk_product_type` = 0
ORDER BY
    p.ref

```

---

<div class="post-metadata">

**Author:** ![aljawaid](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/aljawaid/32/388_2.png) [@aljawaid](https://www.dolibarr.org/forum/u/aljawaid)\
**Post date:** [November 11, 2021, 8:39pm UTC](https://www.dolibarr.org/forum/t/mysql-statements/21415/3 "2021-11-11T20:39:26Z")

</div>

**Products | Incorrect Dimensions**  
`Dolibarr v14.0.2`

```auto
SELECT
    `ref` AS "Product Code",
    `label` AS "Product Name",
    `LENGTH` AS "Length",
    IF(
        `length_units` = -2,
        "CM",
        "Convert VALUE and UNIT to CM"
    ) AS "Length Unit",
    `width` AS "Width",
    IF(
        `width_units` = -2,
        "CM",
        "Convert VALUE and UNIT to CM"
    ) AS "Width Unit",
    `height` AS "Height",
    IF(
        `height_units` = -2,
        "CM",
        "Convert VALUE and UNIT to CM"
    ) AS "Height Unit",
    `weight` AS "Weight",
    IF(
        `weight_units` = 0,
        "KG",
        "Convert VALUE and UNIT to KG"
    ) AS "Weight Unit"
FROM
    `llx_product`
WHERE
    `fk_product_type` = 0 AND(
        `length_units` != -2 OR `width_units` != -2 OR `height_units` != -2 OR `weight_units` != 0 OR `length` != NULL
    ) AND(
        `width` != NULL OR `height` != NULL OR `length` != 0 OR `width` != 0 OR `height` != 0
    )
ORDER BY
    `ref`

```

---

<div class="post-metadata">

**Author:** ![aljawaid](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/aljawaid/32/388_2.png) [@aljawaid](https://www.dolibarr.org/forum/u/aljawaid)\
**Post date:** [November 12, 2021, 8:23am UTC](https://www.dolibarr.org/forum/t/mysql-statements/21415/4 "2021-11-12T08:23:35Z")

</div>

**Product Manufacturing Countries**  
This table shows the total number of products associated to each country.  
`Dolibarr v14.0.2`

```auto
SELECT 
    llx_product.fk_country AS 'Database Country Code',
    llx_c_country.label AS 'Country',
    COUNT(*) AS 'N° of Products'
FROM
    llx_product
        INNER JOIN
    llx_c_country ON llx_product.fk_country = llx_c_country.rowid
GROUP BY fk_country
ORDER BY COUNT(*) DESC;

```