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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 T-SQL Join Issue
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

58 Posts

Posted - 03/08/2013 :  16:06:05  Show Profile  Reply with Quote
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.

Posting Yak Master

101 Posts

Posted - 03/08/2013 :  16:43:18  Show Profile  Reply with Quote

This should do it

		Allocation a 
		OnePercentAllocationApproval o ON a.MoxyOrderID = o.MoxyOrderID
		OnePercentAllocation opa ON o.AllocationGroupID = opa.AllocationGroupID
		 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

Yak Posting Veteran

58 Posts

Posted - 03/08/2013 :  17:05:34  Show Profile  Reply with Quote

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

Posting Yak Master

101 Posts

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

Bustaz Kool
Flowing Fount of Yak Knowledge

1834 Posts

Posted - 03/08/2013 :  18:08:22  Show Profile  Reply with Quote
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

Edited by - Bustaz Kool on 03/08/2013 18:09:37
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000