Multi-Part identifier could not be bound?

[tqeonline]

Hello all - i'm trying to do a left outer join onto an Alias table and it is telling me:

The multi-part identifier "US.RQ_REQ_ID" could not be bound

Here is the query:

SELECT Null as ID,
	Null as Parent_ID,
	US.RQ_REQ_NAME as 'Name',
	MIN(RCYC.RCYC_START_DATE) as 'Start Date',
	MAX(RCYC.RCYC_END_DATE) as 'End Date',
	COUNT(DEF.LN_BUG_ID) as '# Defects',
	COUNT(COV.RC_ENTITY_ID) as '# Tests',
	round(isnull(cast(sum(case when TEST.TC_STATUS = 'Passed' then 1 end) as float),0) / isnull(cast(COUNT(COV.RC_ENTITY_ID) as float),1),2)*100 as '% Complete',
	RF.RQ_REQ_NAME as Primary_Ident,
	NULL as Secondary_Ident
FROM REQ US, REQ RF, REQ IT, REQ PRJ, REQ_COVER COV, TESTCYCL TEST, REQ_RELEASES RD, RELEASES REL, RELEASE_CYCLES RCYC

LEFT OUTER JOIN(SELECT LK.LN_ENTITY_ID, LK.LN_BUG_ID, BG.BG_STATUS
FROM [LINK] LK
  
  LEFT OUTER JOIN (SELECT BG_BUG_ID, BG_STATUS FROM BUG) BG
  ON LK.LN_BUG_ID = BG.BG_BUG_ID
  
WHERE LN_ENTITY_TYPE = 'REQ'
--AND BG.BG_STATUS <> 'New'
AND BG.BG_STATUS <> 'Invalid'
AND BG.BG_STATUS <> 'Deferred'
AND BG.BG_STATUS <> 'Closed'
) DEF
ON US.RQ_REQ_ID = DEF.LN_ENTITY_ID


WHERE IT.RQ_FATHER_ID = PRJ.RQ_REQ_ID
AND RF.RQ_FATHER_ID = IT.RQ_REQ_ID
AND US.RQ_FATHER_ID = RF.RQ_REQ_ID
AND COV.RC_REQ_ID = US.RQ_REQ_ID
AND TEST.TC_TESTCYCL_ID = COV.RC_ENTITY_ID 
AND US.RQ_REQ_ID = RD.RQRL_REQ_ID
AND RD.RQRL_RELEASE_ID = REL.REL_ID
AND REL.REL_ID = RCYC.RCYC_PARENT_ID
AND PRJ.RQ_FATHER_ID = '2517'
AND COV.RC_ENTITY_TYPE = 'TESTCYCL'
GROUP BY PRJ.RQ_REQ_NAME, IT.RQ_REQ_NAME, RF.RQ_REQ_NAME, US.RQ_REQ_NAME

Is there any way to work around this? I need to join the DEF table to the US table based on those two fields.

- 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

 

[tqeonline]

I should have clarified. The part that is causing the issue is:

LEFT OUTER JOIN(SELECT LK.LN_ENTITY_ID, LK.LN_BUG_ID, BG.BG_STATUS
FROM [LINK] LK
  
  LEFT OUTER JOIN (SELECT BG_BUG_ID, BG_STATUS FROM BUG) BG
  ON LK.LN_BUG_ID = BG.BG_BUG_ID
  
WHERE LN_ENTITY_TYPE = 'REQ'
--AND BG.BG_STATUS <> 'New'
AND BG.BG_STATUS <> 'Invalid'
AND BG.BG_STATUS <> 'Deferred'
AND BG.BG_STATUS <> 'Closed'
) DEF
ON US.RQ_REQ_ID = DEF.LN_ENTITY_ID

more precisely

ON US.RQ_REQ_ID = DEF.LN_ENTITY_ID

- 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

 

[Olaf Doschke]

Looks ok, as long as the REQ table you aliased with US earlier by "FROM REQ US" is having a field RQ_REQ_ID. So does the database have a field REQ.RQ_REQ_ID ?

If so, there might be a missing paranthesis somewhere not easy to spot. Reformat to have opening and closing paranthesis with the same indentation and you'd be able to spot that more easy.

Bye, Olaf.

 

[Olaf Doschke]

Later on in the where caluse the RQ_REQ_ID field is given as PRJ.RQ_REQ_ID, IT.RQ_REQ_ID, RF.RQ_REQ_ID and also again US.RQ_REQ_ID, but on the other side as US.RQ_FATHER_ID. It would be a strange join, if US.RQ_FATHER_ID and US.RQ_REQ_ID are present in the same table. I'd guess both the JOIN clause and the Where clause are somewhat dearranged. But nobody can tell you without knowledge about the table schema and meaninggs of the fields, especially key fields used for joins.

Bye, Olaf.

 

[Qik3Coder]

Don't mix join types. It's dirty.
Do you still have a problem after running this?

SELECT Null as ID,    Null as Parent_ID,    
US.RQ_REQ_NAME as 'Name',    MIN(RCYC.RCYC_START_DATE) as 'Start Date',    
MAX(RCYC.RCYC_END_DATE) as 'End Date',    
COUNT(DEF.LN_BUG_ID) as '# Defects',   
 COUNT(COV.RC_ENTITY_ID) as '# Tests',    
 round(isnull(cast(sum(case when TEST.TC_STATUS = 'Passed' then 1 end) as float),0) / isnull(cast(COUNT(COV.RC_ENTITY_ID) as float),1),2)*100 as '% Complete',    
 RF.RQ_REQ_NAME as Primary_Ident,    NULL as Secondary_Ident
 FROM REQ US 
 JOIN REQ RF ON
	US.RQ_FATHER_ID = RF.RQ_REQ_ID
 JOIN REQ IT ON
	RF.RQ_FATHER_ID = IT.RQ_REQ_ID
 JOIN REQ PRJ ON
	IT.RQ_FATHER_ID = PRJ.RQ_REQ_ID
 JOIN REQ_COVER COV ON
	COV.RC_REQ_ID = US.RQ_REQ_ID
 JOIN TESTCYCL TEST ON
	TEST.TC_TESTCYCL_ID = COV.RC_ENTITY_ID 
 JOIN REQ_RELEASES RD ON
	US.RQ_REQ_ID = RD.RQRL_REQ_ID
 JOIN RELEASES REL ON
	RD.RQRL_RELEASE_ID = REL.REL_ID
 JOIN RELEASE_CYCLES RCYC ON
	REL.REL_ID = RCYC.RCYC_PARENT_ID
 LEFT JOIN(
	SELECT LK.LN_ENTITY_ID, LK.LN_BUG_ID, BG.BG_STATUSFROM [LINK] LK    
	LEFT OUTER JOIN (
		SELECT BG_BUG_ID, BG_STATUS 
		FROM BUG) BG  ON
		LK.LN_BUG_ID = BG.BG_BUG_ID  
	WHERE LN_ENTITY_TYPE = 'REQ'
	--AND BG.BG_STATUS <> 'New'
	AND BG.BG_STATUS <> 'Invalid'
	AND BG.BG_STATUS <> 'Deferred'
	AND BG.BG_STATUS <> 'Closed') DEF ON 
	US.RQ_REQ_ID = DEF.LN_ENTITY_ID
WHERE 
	AND PRJ.RQ_FATHER_ID = '2517'
	AND COV.RC_ENTITY_TYPE = 'TESTCYCL'
GROUP BY PRJ.RQ_REQ_NAME, IT.RQ_REQ_NAME, RF.RQ_REQ_NAME, US.RQ_REQ_NAME

Lod

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD