Topic: проблема с отчетом SQL
День добрый, все вроде работает, но есть баг небольшой: если используется 1 работа и 1 деталь в ремонте, то в отчет все падает как надо, но если используется несколько деталей или работ, то падает только последняя запись и общая цена. Где то намудрено с запросом. Не посмотрите на досуге?
С Уважением Дмитрий
SELECT
"is_detall" as uniontype,
"" as raboty_name,
"" as raboty_price,
detall.name as detall_name,
detall.price as detall_price,
ifnull ((SELECT SUM(detall.price) FROM detall WHERE detall.id_application = application.id),0) as SumDetallPrice,
ifnull ((SELECT SUM(raboty.price) FROM raboty WHERE raboty.id_application = application.id),0) as SumRabotyPrice,
srochnost.srochno,
master.master,
detall.name,
tip.tip,
application.date_out,
application.date_dozvona,
application.date,
application.record_count,
application.clientname,
application.SN,
application.komplekt,
application.clientmessage,
brand.brandname,
application.clientphone,
application.vnVid,
application.workname,
application.planDAta,
application.model
FROM
application
LEFT OUTER JOIN srochnost ON srochnost.id = application.id_srochnost
LEFT OUTER JOIN tip ON tip.id = application.id_tip
LEFT OUTER JOIN brand ON brand.id = application.id_brand
LEFT OUTER JOIN detall ON detall.id_application = application.id
LEFT OUTER JOIN master ON master.id = application.id_master
WHERE
application.date_out >={dtFrom1} AND application.date_oplata <={dtTo1} AND application.id_master = {cbMaster1} AND detall.id_application = application.id
GROUP BY application.id
UNION
SELECT
"is_raboty" as uniontype,
raboty.name as raboty_name,
raboty.price as raboty_price,
"" as detall_name,
"" as detall_price,
ifnull ((SELECT SUM(detall.price) FROM detall WHERE detall.id_application = application.id),0) as SumDetallPrice,
ifnull ((SELECT SUM(raboty.price) FROM raboty WHERE raboty.id_application = application.id),0) as SumRabotyPrice,
srochnost.srochno,
detall.name,
master.master,
tip.tip,
application.date_out,
application.date_dozvona,
application.date,
application.record_count,
application.clientname,
application.SN,
application.komplekt,
application.clientmessage,
brand.brandname,
application.clientphone,
application.vnVid,
application.workname,
application.planDAta,
application.model
FROM
application
LEFT OUTER JOIN srochnost ON srochnost.id = application.id_srochnost
LEFT OUTER JOIN tip ON tip.id = application.id_tip
LEFT OUTER JOIN brand ON brand.id = application.id_brand
LEFT OUTER JOIN raboty ON raboty.id_application = application.id
LEFT OUTER JOIN master ON master.id = application.id_master
LEFT OUTER JOIN detall ON detall.id_application = application.id
WHERE
application.date_out >={dtFrom1} AND application.date_oplata <={dtTo1} AND application.id_master = {cbMaster1} AND detall.id_application = application.id
GROUP BY application.id
