I form a summary report on the hours of work and downtime of construction machines at the facilities. The user enters in the form on the site for one machine name information about how much the machine worked and stood on a particular day, indicating the causes of downtime. Information from the form is entered in the jos_addRabotyAndProstoyMain , HoursRaboty (so as not to pile everything up in one table), and the causes of downtime and hours of downtime from the same form are in the jos_addRabotyAndProstoySecond table due to the fact that one machine may have several causes of downtime.

DB schema: enter image description here

The problem is that in the jos_addRabotyAndProstoySecond table in the Prostoy and id_fk foreign keys there are duplicate values ​​and the INNER JOIN does not work correctly (duplicating of the rows that could be eliminated in the initial SELECT by the DISTINCT operator) occurs. In the final report (link to sql fiddle below) an error in the amount of hours in the columns of Причины простоев and Причины простоев . Instead of 12 hours for KAMAZ, it displays 10, and the reasons for the downtime are also given a total of 10 (by the way, grouping cannot be done correctly for reasons due to two foreign keys, help is also needed). For some reason, a row in the jos_addRabotyAndProstoySecond table is jos_addRabotyAndProstoySecond under id = 70

Request:

 SELECT avto.Marka AS Марка, SUM(DISTINCT HR.otrab) as 'Часы работы ',SUM(sec.Hours1) as Простои,CONVERT(group_concat(DISTINCT concat(prostoy.Prichina,'=',sec.Hours1) separator ';') USING 'utf8') as 'Причины простоев',CONVERT(group_concat(DISTINCT concat(HR.VipolnennayaRabota,'=',HR.v_rab) separator ';') USING 'utf8') as 'Выполненные работы' FROM jos_addRabotyAndProstoyMain main INNER JOIN avto ON main.Marka=avto.ID_Avto INNER JOIN ( SELECT VipolnennayaRabota, SUM(V_raboty) as v_rab,SUM(OtrabotanoMachine_hours) as otrab,id_main FROM HoursRaboty GROUP BY VipolnennayaRabota) HR ON HR.id_main=main.id INNER JOIN ( SELECT Prostoy,SUM(Hours)as Hours1,id_fk FROM jos_addRabotyAndProstoySecond GROUP BY id_fk,Prostoy)sec ON main.id = sec.id_fk INNER JOIN prostoy on sec.Prostoy = prostoy.ID_Prosyoy GROUP BY avto.Marka

To test Sql Fiddle

I really need help already broken head, I can not make a week.

    1 answer 1

    INNER JOIN works exactly as it should . He is obliged to duplicate the rows, because as for each of the records of table A, he must take each of the records of table B. In this case, if one of the tables does not have records for splicing, it should not produce a row at all (because of this you have a problem with id = 70, because because of an error in the grouping of the work performed (you did not include id_main in the group by), you simply do not have an entry for one of the machines in the works).

    In order for the JOIN produce records, even if there are no matching entries in the second table, you should use the LEFT JOIN .

    In order for the records not to be multiplied, you need to submit the JOIN exactly one record for the join conditions, i.e. they must be grouped to the desired level in advance. In principle, it is possible that there would be several rows in one of the tables, but then you have to correctly arrange the sum and max , depending on whether there are many entries in a particular table or one.

    And your request should look like this (I also took the liberty to change the v_rab calculation, otherwise you took one field by type of work, and for the total amount you used the amount of something suspiciously similar to the type of work ID):

     SELECT avto.marka as Марка,v.otrab as 'Часы работы',s.hours as Простои, s.g_hours as 'Причины простоев',v.v_rab as 'Выполненные работы' FROM avto LEFT JOIN ( SELECT marka,CONVERT(group_concat(concat(VipolnennayaRabota,'=',otrab) separator ';') USING 'utf8') v_rab,SUM(otrab) as otrab FROM ( SELECT marka,VipolnennayaRabota, SUM(V_raboty) as v_rab, SUM(OtrabotanoMachine_hours) as otrab FROM HoursRaboty h,jos_addRabotyAndProstoyMain m WHERE h.id_main=m.id GROUP BY marka,VipolnennayaRabota ) V GROUP BY marka ) v ON v.marka=avto.id_avto LEFT JOIN ( SELECT marka,sum(s.Hours) hours, CONVERT(group_concat(concat(p.Prichina,'=',s.Hours) separator ';') USING 'utf8') g_hours FROM ( SELECT marka,Prostoy,SUM(Hours) as Hours FROM jos_addRabotyAndProstoySecond s, jos_addRabotyAndProstoyMain m WHERE s.id_fk=m.id GROUP BY marka,Prostoy ) s, prostoy p WHERE s.Prostoy = p.ID_Prosyoy GROUP BY marka ) s ON s.marka=avto.id_avto
    • I apologize for misleading with the column “ Выполненные работы it does not count hours of work, but the sum of the amount of work in different units for the sum of hours of shifts. There was only the problem of простоях and the причины простоев . I reduced the report to almost a minimum because of this it became illogical, and the report itself consists of 14 columns. The request has changed a little for its task, everything works, thank you so much for your help and detailed explanation. Left join I do not understand, I tried to do without him. - Vitaly
    • @Vitaly In this case, the left may not be particularly needed, rather reinsurance. Its functionality is necessary if for some brand there will be an operating time, but there will be no downtime. or vice versa, simple without time. Regular join in this case will not show the whole record - Mike