Looking for help with SQL statement

Question

Looking for help with SQL statement

DalF1960

SSC Enthusiast

Points: 158
More actions
November 23, 2025 at 5:26 pm

#4689420
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
- This topic was modified 3 weeks ago by DalF1960.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63627 More actions · Answer 1

The most obvious thing is that you're using INNER joins everywhere. If you want to keep all the records from one table, you need to OUTER join from that to the rest. So instead of this:

FROM stockallocations sa
[INNER] JOIN worksorders wo ON
   sa.worksorderid = wo.id

You may want to try this:

FROM stockallocations sa
LEFT JOIN worksorders wo
   ON sa.worksorderid = wo.id

DalF1960 SSC Enthusiast Points: 158 More actions · Answer 2

Hi Pietlinden

So grateful for your input, however I have changed the SQL as suggested and it still hasn't returned the missing row in the stockallocations table. I have read about the UNION, COALESCE features and wondered if this may help. However as someone who is not as technical as I need to be I am unsure how I could incorporate this in my query 🙁

best regards Dal

homebrew01 SSC Guru Points: 55567 More actions · Answer 3

Can uou post your new code ? ( In a more readable format)

When troubleshooting, I often comment out much of the code, then add it back in 1 at a time to see the differnt results.

Lowell SSC Guru Points: 323753 More actions · Answer 4

i see a couple of things. try my query below.

the first issue, is making sure all your joins switch to left joins, as everyone else pointed out

second, your WHERE statement has WHERE wo.type = 'SALES ORDER' that converts the left join to an inner join, as if there's nor records, you lose that record from stockallocations that has no work order.

see how i moved that WHERE into the LEFT JOIN higher in the query.

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
LEFT JOIN worksorders wo ON sa.worksorderid = wo.id  AND wo.type = 'SALES ORDER'
LEFT JOIN allpartmaster apm ON sa.partid = apm.partnum
LEFT JOIN salesorderitems soi ON wo.orderid = soi.orderid
LEFT JOIN salesorders so ON wo.orderid = so.id
LEFT JOIN stockedparts sp ON apm.partnum = sp.partid
LEFT JOIN ( SELECT
sb.partid,
MAX(sb.unitvalue) AS Max_Batchvalue
FROM stockbatches sb
LEFT JOIN stockallocations sa ON sa.partid = sb.partid
GROUP BY sb.unitvalue, sb.partid
) sub ON
sub.partid = sa.partid
LEFT JOIN traders t ON
wo.traderid = t.id
LEFT JOIN users u ON
so.responsibility = u.id
WHERE 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

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pietlinden SSC Guru Points: 63627 More actions · Answer 5

I started reformatting the code... here's my reformat (FWIW):

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
INNER 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
INNER JOIN 
( 
/* I think the problem is here... INNER joins will remove any records from both tables where there's no match in the join clause (ON tableA.column1 = tableB.column2)
*/
SELECT
sb.partid,
MAX(sb.unitvalue) AS Max_Batchvalue
FROM stockbatches sb
LEFT /* INNER */ 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];

If you want to keep ALL of the records from one table and only the matching ones from the other, you want an OUTER join, not an INNER join.

I agree with Homebrew... I'd start with just the two tables (StockAllocations and StockBatches).

SELECT a.*, b.*
FROM StockAllocations a
LEFT JOIN StockBatches b
ON...

Not sure this will be popular, but one thing I like to do with complicated queries with lots of nested chunks is to create Common Table Expressions for the chunks and test them individually and then join them in the final query. Just makes debugging waaaay easier.

ScottPletcher SSC Guru Points: 101221 More actions · Answer 6

I identified the changes I made to the original code using /*--<<--*/ on/around them. This code should also perform better.

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,
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

/*--<<--*/
LEFT OUTER JOIN ( SELECT
sb.partid,
MAX(sb.unitvalue) AS Max_Batchvalue
FROM stockbatches sb
GROUP BY 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'
/*ORDER BY sa.partid, ...*/

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".