I'm doing something wrong with my Join Statement using the WITH.
My OnePercentAllocation with that specific AllocationGroupID returns 286 rows and I want to show all these rows.
My SummaryAllocation table derived from the WITH statement has 200 rows
All I want is to return all 286 rows and ClientID with no AllocQty will just show up as NULL.
WITH SummaryAllocation AS (SELECT OnePercentAllocationApproval.AllocationGroupID, Allocation.Autex, Allocation.AllocQty
FROM Allocation INNER JOIN
OnePercentAllocationApproval ON Allocation.MoxyOrderID = OnePercentAllocationApproval.MoxyOrderID
WHERE (OnePercentAllocationApproval.MoxyOrderID = @MoxyOrderID))
SELECT SummaryAllocation_1.AllocQty, OnePercentAllocation.ClientID
FROM OnePercentAllocation LEFT OUTER JOIN
SummaryAllocation AS SummaryAllocation_1 ON OnePercentAllocation.AllocationGroupID = SummaryAllocation_1.AllocationGroupID
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.