Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 T-SQL Join Issue

Author  Topic 

pvong
Yak Posting Veteran

58 Posts

Posted - 2013-03-08 : 16:06:05
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.

Robowski
Posting Yak Master

101 Posts

Posted - 2013-03-08 : 16:43:18
Hi,

This should do it


SELECT
o.AllocationGroupID
,a.Autex
,a.AllocQty
,opa.ClientID
FROM
Allocation a
INNER JOIN
OnePercentAllocationApproval o ON a.MoxyOrderID = o.MoxyOrderID
LEFT JOIN
OnePercentAllocation opa ON o.AllocationGroupID = opa.AllocationGroupID
WHERE
o.MoxyOrderID = @MoxyOrderID


However if you need to use a common table expression (with statement)

Just swap your final left join around to be

SummaryAllocation_1.AllocationGroupID = OnePercentAllocation.AllocationGroupID

I'm guessing there are only 200 records in OnePercentAllocation and OnePercentAllocationApproval with the specific ID returns 286 rows?
Go to Top of Page

pvong
Yak Posting Veteran

58 Posts

Posted - 2013-03-08 : 17:05:34
Robowski,

Neither of the methods worked. I just get thousands and thousands of rows and it just keeps going.

The WITH Expression locks the returned rows to where AllocationGroupID = 1 which is what OnePercentAllocation is using to filter that table by.



------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 2013-03-08 : 17:16:41
How many rows return when you run your query?
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-03-08 : 18:08:22
You are using OnePercentAllocation as the preserved table and LEFT JOINING your CTE to it. You should expect to get every row of the OnePercentAllocation table, which I assume you are getting. It's not clear to me what results you want to get but if you'll elaborate, perhaps we can arrive at a solution. Did you perhaps want to use a RIGHT OUTER JOIN?

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page
   

- Advertisement -