Query shows different data each time I execute Mysql

Question

Query shows different data each time I execute Mysql

Navigation

#1 by (0 votes)

0

I have the following query:

SELECT pr_production_units_details.id AS idProductionUnit, pr_production_units_details.production_units_detail AS productionUnit,
IF(pr_varieties.variety IS NULL, 'SIN SEMBRAR O ERR', pr_varieties.variety) AS variety
FROM pr_production_units_details
LEFT JOIN (
SELECT MAX(sw_sowing.id) AS ids, sw_sowing.id_production_unit_detail,  sw_sowing.id_variety
FROM sw_sowing
WHERE sw_sowing.status != 0
AND sw_sowing.id_tenant = 1
AND YEARWEEK(sw_sowing.date) <= 201741
GROUP BY sw_sowing.id_production_unit_detail, id_variety
ORDER BY ids DESC
) AS sw ON pr_production_units_details.id = sw.id_production_unit_detail
INNER JOIN pr_varieties ON sw.id_variety = pr_varieties.id
WHERE pr_production_units_details.id_grouper_detail = 1
AND pr_production_units_details.status = 100
AND pr_production_units_details.id_tenant = 1
GROUP BY pr_production_units_details.id

that brings me the following result:

------------------------------------------------
  idProductionUnit | productionUnit |  variety
------------------------------------------------
        1          |        1       |   YELLOW 
------------------------------------------------
        2          |        2       |   YELLOW 
------------------------------------------------
        3          |        3       |   YELLOW 
------------------------------------------------

The previous result is fine, but every time I run the column variety changes its values, that is,

------------------------------------------------
  idProductionUnit | productionUnit |  variety
------------------------------------------------
        1          |        1       |   YELLOW 
------------------------------------------------
        2          |        2       |   BLUE 
------------------------------------------------
        3          |        3       |   YELLOW 
------------------------------------------------

or you can also leave like this;

------------------------------------------------
  idProductionUnit | productionUnit |  variety
------------------------------------------------
        1          |        1       |   BLUE 
------------------------------------------------
        2          |        2       |   YELLOW 
------------------------------------------------
        3          |        3       |   YELLOW 
------------------------------------------------

I do not know if I have to see the ORDER BY or the GROUP BY but I have not understood because it shows me different data.

mysql sql

asked by Fabian Sierra 17.10.2017 в 16:34

source

1 answer

Show the name of the brand with a link with the plugin: Product Brands For WooCommerce Help with exception ADMA0043E, when trying a remote deploy with WAS7

score 0 · Accepted Answer

I could already solve the problem, I understand that it is because of the GROUP BY that I have in LEFT JOIN so I changed the query in the following way:

SELECT  pr_production_units_details.id AS idProductionUnit, pr_production_units_details.production_units_detail AS  productionUnit, pr_varieties.variety
FROM (SELECT MAX(sw_sowing.id) AS id
            FROM sw_sowing
            WHERE sw_sowing.status != 0
            AND sw_sowing.id_tenant = 1
            AND YEARWEEK(sw_sowing.date) <= 201741
            GROUP BY  sw_sowing.id_production_unit_detail
)AS sw
INNER JOIN sw_sowing ON sw_sowing.id = sw.id
INNER JOIN pr_production_units_details ON pr_production_units_details.id = sw_sowing.id_production_unit_detail
INNER JOIN pr_varieties ON pr_varieties.id = sw_sowing.id_variety
WHERE sw_sowing.status != 0 
AND sw_sowing.id_tenant =1
AND pr_production_units_details.id_grouper_detail = 13
GROUP BY pr_production_units_details.id, variety

I just need the condition to know if Variety is NULL to put 'NOT SOWED' but that I do it internally.