Left Outer Join no liking Alias.Fieldname

[tqeonline]

I have a query that works if i take out the LEft Out Join. The only thing underlined is "c.CY_USER_05" can someone tell me what i'm messing up?

--Project Level Data
select c.CY_User_05 as 'Project', 
MIN(r.Rel_Start_Date) as 'Start Date', --change to REL_USER_02 on live DB
MAX(r.Rel_End_Date) as 'End Date', --change to REL_USER_05 on live DB
count(distinct c.CY_USER_06) as '# Iterations', 
count(distinct r.REL_NAME) as '# Releases', 
count(distinct c.CY_CYCLE) as '# Requirements', 
count(distinct ts.TS_NAME) as '# Tests', 
sum(case when tc.TC_STATUS = 'Passed' then 1 end) as '# Passed', 
sum(case when tc.TC_STATUS = 'Failed' then 1 end) as '# Failed',
round(cast(sum(case when tc.TC_STATUS = 'Passed' then 1 end) as float) / cast(count(distinct ts.TS_NAME) as float),2)*100 as '% Complete',
count(distinct b.bg_bug_id) as '# Defects'
FROM dbo.test ts
, dbo.CYCLE c
, dbo.testcycl tc
, dbo.RELEASE_CYCLES rc
, dbo.RELEASES r
left outer join (SELECT [BG_BUG_ID]
      ,[BG_STATUS]
      ,[BG_USER_11]
      ,[BG_PROJECT]
      ,[BG_DETECTED_IN_REL]
      ,[BG_DETECTED_IN_RCYC]
	FROM dbo.BUG b
	, dbo.test ts
	, dbo.CYCLE c
	, dbo.testcycl tc
	, dbo.RELEASE_CYCLES rc
	, dbo.RELEASES r
	where b.BG_USER_11 = c.CY_CYCLE 
	and b.BG_PROJECT = c.CY_User_05
	and b.BG_DETECTED_IN_REL = r.REL_ID
	and b.BG_DETECTED_IN_RCYC = rc.RCYC_ID) as b
	on b.bg_project = c.CY_USER_05
where ts.ts_test_id = tc.tc_test_id
and c.cy_cycle_id = tc.tc_cycle_id
and tc.TC_ASSIGN_RCYC= rc.RCYC_ID
and rc.RCYC_PARENT_ID = r.REL_ID
and c.CY_USER_05 IS NOT NULL
Group By c.cy_user_05

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[r937]

that subquery looks ~very~ suspicious

the subquery references six tables, but you are only joining "b" to "c", "r", and "rc" -- you have no join conditions for "ts" and "tc" and consequently you will get humoungous cross join effects in the subquery

if i knew what you were trying to do, i might be able to help you

but i suspect that you shouldn't actually be trying to join all those tables in the subquery

here's a tip -- never use the same table aliases inside a subquery as in the main query, it's far too easy for either you or the software to get confused

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon