Joining multiple tables / sum

[lukefuller]

Hi All,

I have been pulling my hair out over this one.

What we are trying to do is pull sum values for the same item out of three different tables in the same DB (MSSQL '05) – Should be quite straight forward however...

The result should look a bit like below;

item code | initial population value | ordered value | sold value | cost price

The below query is pulling everything correctly except ordered value.

I have a suspicion the below snippet of the statement is to blame for this – the value that return from this below snippet are inflated by many multiples.

LEFT OUTER JOIN
Sys2OrderItems ON Sys2OrderItems.bc_code = BaseListing.Code AND Sys2OrderItems.status = 2
INNER JOIN
Sys2Order ON Sys2OrderItems.order_number = Sys2Order.order_number AND 
Sys2Order.order_location = @LocationNumber AND Sys2Order.order_approval_status = 3 AND 
Sys2Order.order_status = 3 AND Sys2Order.order_date > @InternalDate

I believe what may be happening is if the Order (Sys2Order) has multiple items (Sys2OrderItems) it is calculating the SUM value by the amount of items in the order to due to the number of rows in Sys2OrderItems for that ordernumber.

I hope this makes sense however been on this all day and may be totally wrong.

Any help greatly appreciated.

Full query code below;

SELECT BaseListing.Code, InitialValues.value AS InitialPopulation, SUM(Sys2InvoiceItems.Quantity) AS Sold, 
SUM(Sys2OrderItems.qty) AS recv, Sys1Item.Description, ItemGroupsParent.Name AS PrimaryGroup, 
ItemGroupsChild.Name AS ChildGroup, Sys2Item.Cost_Group1, Sys2Item.Cost_Group2, 
SUM(Sys2OrderItems.qty) AS Expr1
FROM BaseListing
LEFT OUTER JOIN
InitialValues ON BaseListing.Code = InitialValues.code AND 
InitialValues.location = @LocatioNumber
LEFT OUTER JOIN
Sys1Item ON Sys1Item.ItemNo = BaseListing.Code AND Sys1Item.ItemTypeID = 15 AND Sys1Item.Deleted = 0 LEFT OUTER JOIN
Sys2InvoiceItems ON Sys2InvoiceItems.ItemID = Sys1Item.ItemID AND Sys2InvoiceItems.EntryDate > 733953
LEFT OUTER JOIN
Sys2Invoices ON Sys2Invoices.InvoiceID = Sys2InvoiceItems.InvoiceID AND Sys2Invoices.EntryDate > 733953 LEFT OUTER JOIN
Sys2Accounts ON Sys2Accounts.RoomID = Sys2Invoices.RoomID AND Sys2Accounts.Deleted = 0 AND 
Sys2Accounts.Inactive = 0
LEFT OUTER JOIN
Sys2OrderItems ON Sys2OrderItems.bc_code = BaseListing.Code AND Sys2OrderItems.status = 2 INNER JOIN
Sys2Order ON Sys2OrderItems.order_number = Sys2Order.order_number AND 
Sys2Order.order_location = @LocationNumber AND Sys2Order.order_approval_status = 3 AND 
Sys2Order.order_status = 3 AND Sys2Order.order_date > @InternalDate
LEFT OUTER JOIN
Sys2Item ON Sys2Item.Code = BaseListing.Code
LEFT OUTER JOIN
ItemGroupsParent ON ItemGroupsParent.ID = Sys2Item.Primary_ID 
LEFT OUTER JOIN
ItemGroupsChild ON ItemGroupsChild.ID = Sys2Item.Sub_ID
WHERE (BaseListing.IsPhysical = 0)
GROUP BY BaseListing.Code, InitialValues.value, Sys1Item.Description, ItemGroupsParent.Name, 
ItemGroupsChild.Name, Sys2Item.Cost_Group1, Sys2Item.Cost_Group2

[tesuji]

I can't figure out the backgrounds to the extent, so just out of curiosity, why do you using "left outer joins" everywhere? I am sure, you already know this definition of left outer join:

/* [I]The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). 

This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join  returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). 

If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on the right table.[/I] */

If there aren't important reasons, I myself would also examine the result of simple "inner joins".

-- tesu