November 23, 2025 at 5:26 pm
Hi there
I am seeking some help with an SQL statement. Any help would be welcomed. TIA
See the query i have used below.
I have an SQL statement that is trying to return all the values of a column from a table called stockallocations. However as no record exists for one of the records in the stockbatches table it doesn't return as many rows as I expected. I have 20 rows in the stockallocations table but only 19 rows are returning results as the 20th record is not is the stockbatches table. For this 20th Row I would like to use a substitute value from allpartmaster table when no record exists in the stockbatches table.
How can i achieve this?
SELECT
sa.partid AS ALLOC_Part,
sa.quantity,
sa.ordertype,
sa.worksorderid,
sa.reference,
sa.stockvalue,
apm.partdesc,
soi.orderid,
soi.itemnumber,
soi.partid AS SOI_Part,
so.traderid,
t.name,
u.name As SalesRep,
MAX(sub.Max_Batchvalue) AS High_Batchvalue
FROM stockallocations sa
JOIN worksorders wo ON
sa.worksorderid = wo.id
INNER JOIN allpartmaster apm ON
sa.partid = apm.partnum
INNER JOIN salesorderitems soi ON
wo.orderid = soi.orderid
INNER JOIN salesorders so ON
wo.orderid = so.id
INNER JOIN stockedparts sp ON
apm.partnum = sp.partid
JOIN ( SELECT
sb.partid,
MAX(sb.unitvalue) AS Max_Batchvalue
FROM stockbatches sb
JOIN stockallocations sa ON
sa.partid = sb.partid
GROUP BY sb.unitvalue, sb.partid
) sub ON
sub.partid = sa.partid
INNER JOIN traders t ON
wo.traderid = t.id
INNER JOIN users u ON
so.responsibility = u.id
WHERE
wo.type = 'SALES ORDER'
and
sa.worksorderid LiKE 'W-010049'
GROUP BY
sa.partid,
sa.quantity,
sa.ordertype,
sa.worksorderid,
sa.reference,
sa.stockvalue,
apm.partdesc,
soi.orderid,
soi.itemnumber,
soi.partid,
so.traderid,
t.name,
u.name
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply