Hello!
I made query for a while loop in my test environment (with MariaDB 10.1.3) and all worked good!
Now i moved same code to main website (with MySQL 5.7.27) and this is where it gets weird
Result now given on every refresh changes. âŚ
Query has multiple LEFT JOIN's
(some of them are union)
Query:
SELECT l.id AS lid,
l.docNr AS receive_docNr,
l.thisTransport AS receive_transport,
l.productNr AS receive_productNr,
l.activityDate AS receive_date,
l.issueDate,
h.ladingNr AS receive_ladingNr,
l.place_count AS receive_place_count,
h.cargo_status AS receive_cargo_status,
h.clientCode AS clientCode,
e.id AS eid,
e.docNr AS docNr,
e.deliverydate AS deliveryDate,
e.activitydate AS activityDate,
e.assistant_amount AS assistant_amount,
e.amount AS amount,
e.productnr AS productNr,
e.productumo AS productUmo,
e.status AS status,
e.orgline,
ni.name1,
ni.name2,
h.agreements AS agreements,
nc.Name,
a.uom AS uom,
au.uom AS auuom,
ez.status AS istatus,
ez.place_count AS iplace_count,
ez.activityDate AS iactivityDate,
ez.place_count AS iassistant_amount
FROM cargo_line AS l
LEFT JOIN (
(
SELECT id,
docNr,
deliverydate,
activitydate,
assistant_amount,
amount,
productnr,
productumo,
status,
orgLine,
resource,
cargoLine AS mold
FROM item_ledger_entry
)
UNION
(
SELECT id,
docNr,
deliverydate,
activitydate,
assistant_amount,
amount,
productnr,
productumo,
status,
orgLine,
resource,
orgLine AS mold
FROM item_ledger_entry
)
) AS e
ON e.mold = l.id
LEFT JOIN (
(
SELECT status,
orgLine,
place_count,
activityDate,
cargoLine AS mold
FROM item_ledger_entry
)
UNION
(
SELECT status,
orgLine,
place_count,
activityDate,
orgLine AS mold
FROM item_ledger_entry
)
) AS ez
ON ez.mold = l.id
LEFT JOIN cargo_header AS h
ON l.docNr = h.docNr
LEFT JOIN agreements_lines AS a
ON h.agreements = a.contractnr
AND e.productnr = a.item
AND e.resource = a.service
LEFT JOIN additional_uom AS au
ON e.productnr = au.productnr
AND au.status = 1
AND au.convert_from = 1
LEFT JOIN n_customers AS nc
ON h.clientCode = nc.Code
LEFT JOIN n_items AS ni
ON l.productNr = ni.code
WHERE h.clientCode = '1234567890'
AND h.agreements = 'AGR00001'
#AND DATE_FORMAT(l.issueDate, '%Y-%m-%d') BETWEEN '2020-06-01' AND '2020-06-30'
GROUP BY l.id
ORDER BY l.activityDate ASC, l.docNr
I could not find useful info about why this could why does it happen. âŚ
Anyone has any ideas?
Thanks in advance!