Left Outer Join on Multiple Columns 1

[tqeonline]

Good Morning everyone!

I have a pretty straight forward query:

SELECT NULL as ID,
	NULL as Parent_ID,   
	c.CY_CYCLE as 'Name', 
	NULL as 'Start Date', 
	NULL as 'End Date', 
	isnull(bga.cnt,0) as 'Active Defects',
	round(cast(isnull(tcp.cnt,0) as float)/cast(isnull(tc.cnt,1) as float)*100,2) as '% Complete',
	c.CY_USER_07 as Primary_Ident,
	r.Rel_Name as Secondary_Ident
FROM [RELEASES] r,[RELEASE_CYCLES] rc,[CYCLE] c

LEFT OUTER JOIN (select tc.tc_cycle_ID, COUNT(*) as CNT FROM [TESTCYCL] tc
WHERE tc.TC_STATUS is not null
and tc.TC_CYCLE_ID is not null
and tc.TC_STATUS <> 'N/A'
and tc.TC_STATUS <> 'Deferred'
GROUP BY tc.tc_cycle_ID) as tc
on c.CY_CYCLE_ID = tc.tc_cycle_ID

LEFT OUTER JOIN (select tcp.tc_cycle_ID, COUNT(*) as CNT FROM [TESTCYCL] tcp
where tcp.TC_STATUS = 'Passed'
GROUP BY tcp.tc_cycle_id) as tcp
on c.CY_CYCLE_ID = tcp.tc_cycle_ID

LEFT OUTER JOIN (select bga.bg_detected_in_rcyc, bga.BG_DETECTED_in_rel, bga.BG_PROJECT, COUNT(*) as CNT from [BUG] bga
where bga.BG_STATUS not in ('New', 'Invalid', 'Deferred', 'Closed')
AND bga.BG_USER_17 = 'Defect'
group by bga.bg_detected_in_rcyc, bga.bg_detected_in_rel, bga.bg_project) as bga
on c.CY_ASSIGN_RCYC = bga.BG_DETECTED_IN_RCYC
and c.CY_USER_07 = bga.BG_PROJECT

WHERE r.REL_ID = rc.RCYC_PARENT_ID
and rc.RCYC_ID = c.CY_ASSIGN_RCYC
and c.CY_USER_07 = 'PRJ-02396 2011 - Retail HP & Sub Portal'
Group By  c.CY_USER_07 , r.REL_NAME , rc.rcyc_name, c.CY_CYCLE, tc.cnt, tcp.cnt, bga.cnt

On the left outer join:

LEFT OUTER JOIN (select bga.bg_detected_in_rcyc, bga.BG_DETECTED_in_rel, bga.BG_PROJECT, COUNT(*) as CNT from [BUG] bga
where bga.BG_STATUS not in ('New', 'Invalid', 'Deferred', 'Closed')
AND bga.BG_USER_17 = 'Defect'
group by bga.bg_detected_in_rcyc, bga.bg_detected_in_rel, bga.bg_project) as bga
on c.CY_ASSIGN_RCYC = bga.BG_DETECTED_IN_RCYC
and c.CY_USER_07 = bga.BG_PROJECT

I need it to join on:

on c.CY_ASSIGN_RCYC = bga.BG_DETECTED_IN_RCYC
and c.CY_USER_07 = bga.BG_PROJECT
and r.rel_id = bga.BG_DETECTED_IN_REL

but it doesn't like

r.rel_id

any ideas?

- 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

 

[markros]

Your current query is already a mix of two style JOINS.

Please convert it first to INNER /LEFT JOINs, then we will be able to provide a solution.

PluralSight Learning Library

 

[tqeonline]

like this?

SELECT null as ID, 
	null as PARENT_ID, 
	r.rel_name as NAME, 
	null as [START DATE], 
	null as [END DATE], 
	isnull(bga.cnt,0) as 'Active Defects',
	round(cast(isnull(sum(tcp.cnt),0) as float)/cast(isnull(sum(tc.cnt),1) as float)*100,2) as '% Complete',
	c.cy_user_07 as [PRIMARY_IDENT],
	NULL as [SECONDARY_IDENT]
FROM [RELEASES] r

inner join (select RCYC_PARENT_ID, RCYC_ID from [RELEASE_CYCLES] rc) as rc
on rc.RCYC_PARENT_ID = r.rel_id

inner join (select CY_USER_07, CY_ASSIGN_RCYC, CY_CYCLE_ID from [CYCLE] c) as c
on c.CY_ASSIGN_RCYC = rc.rcyc_id

LEFT OUTER JOIN (select tc.tc_cycle_ID, COUNT(*) as CNT FROM [TESTCYCL] tc
WHERE tc.TC_STATUS is not null
and tc.TC_CYCLE_ID is not null
and tc.TC_STATUS <> 'N/A'
and tc.TC_STATUS <> 'Deferred'
GROUP BY tc.tc_cycle_ID) as tc
on c.CY_CYCLE_ID = tc.tc_cycle_ID

LEFT OUTER JOIN (select tcp.tc_cycle_ID, COUNT(*) as CNT FROM [TESTCYCL] tcp
where tcp.TC_STATUS = 'Passed'
GROUP BY tcp.tc_cycle_id) as tcp
on c.CY_CYCLE_ID = tcp.tc_cycle_ID

LEFT OUTER JOIN (select bga.BG_DETECTED_in_rel, bga.BG_PROJECT, COUNT(*) as CNT from [BUG] bga
where bga.BG_STATUS not in ('New', 'Invalid', 'Deferred', 'Closed')
AND bga.BG_USER_17 = 'Defect'
group by bga.bg_detected_in_rel, bga.bg_project) as bga
on c.CY_USER_07 = bga.BG_PROJECT
and r.REL_ID = bga.BG_DETECTED_IN_REL

WHERE c.CY_USER_07 = 'PRJ-02396 2011 - Retail HP & Sub Portal'
GROUP BY c.CY_USER_07, r.REL_NAME, bga.cnt
ORDER BY NAME

- 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

 

[markros]

Yes, something like this although I don't see a reason for the very first derived table (why not use direct JOIN)?

PluralSight Learning Library

 

[tqeonline]

I don't know to be honest. I have only do SQL for maybe a year and I try to just build as I go - you said make inner joins and so I did. I'm slowly learning the "right" way to do this kind of stuff.

Any advice as far as your statement?

- 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

 

[Qik3Coder]

tge,
Is this still giving you an error?
Normally the error you indicated is because of 1 of 2 things.
A.) You are trying to join on a column before it exists.

select a.col1, b.col2
from tablea a
join tableb b on
a.id = b.id
[!]and b.id = c.id[/!]
join tablec c on
a.id = c.id

or else the column does exist in the table (alias) specified.
Normally this is just mistyping a column name.

Lod

You've got questions and source code. We want both!

 

[tqeonline]

Qik3Coder - it works with me doing the inner joins like markros stated.

I was just diving a little deeper on how I should have done the inner joins for future knowledge.

- 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