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

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

pvong
Starting Member

43 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.

Robowski
Starting Member

49 Posts
Posted - 03/08/2013 :  16:43:18  Show Profile  Reply with Quote
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
Starting Member

43 Posts
Posted - 03/08/2013 :  17:05:34  Show Profile  Reply with Quote
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
Starting Member

49 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

USA
1430 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000