SELECT Min(mainSA.State) as State, 
org.ObjectRetrievalGroupKey as [Queue Id],
MIN(org.ObjectRetrievalGroupDesc) as [Queue Description],
COUNT(DISTINCT innersa.SubjectAlertViolationKey) as NoViolations,
COUNT(DISTINCT innersa.ViolationSubjectKey) as NoUniqueCards

FROM ObjectRetrievalGroup org   
INNER JOIN SubjectAlert mainSA   
ON mainSA.SubjectAlertKey = org.ObjectRetrievalGroupKey

JOIN MemberGroupMember mgm ON mgm.MemberKey = org.MemberKey
INNER JOIN SubjectType st   
ON st.SubjectTypeKey = mainSA.SubjectTypeKey

LEFT JOIN 
(
SELECT v.SubjectAlertKey, v.SubjectAlertViolationKey, v.ViolationSubjectKey, v.RuleKey, v.ViolationSubjectTypeKey
FROM SubjectAlertViolation v JOIN SubjectAlert a  
ON mainSA.SubjectAlertKey = v.SubjectAlertKey

WHERE ((Convert(Char(10),a.RunDate,101) >=  '09/02/2009' AND Convert(Char(10),a.RunDate,101) < '09/02/2009') OR a.RunDate IS NULL)
                 AND (a.AlertTypeId IN ('RTLR','ELEC','PAPR','COLA','TRAN','TRANAUTH') OR a.AlertTypeId IS NULL)
				 AND a.MemberKey IN (select MemberKey from MemberGroupMember WHERE mgm.MemberGroupKey = 1)
) as innersa 

ON innersa.RuleKey = org.ObjectRetrievalGroupKey AND innersa.ViolationSubjectTypeKey = mainSA.SubjectAlertKey

LEFT JOIN SubjectAlert a WITH (NOLOCK) ON a.SubjectAlertKey = innersa.SubjectAlertKey
WHERE st.SubjectTypeId = 'R'
AND mgm.MemberGroupKey =2
AND (a.MemberKey IN (select MemberKey from MemberGroupMember WHERE mgm.MemberGroupKey = 1) OR a.MemberKey IS null)

GROUP BY org.ObjectRetrievalGroupKey, org.MemberKey
HAVING ((Min(mainSA.State) = 'A' ) OR (Min(mainSA.State) <> 'A') AND COUNT(DISTINCT innersa.SubjectAlertViolationKey) > 0)

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "mainSA.SubjectAlertKey" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "mgm.MemberGroupKey" could not be bound.

quote:

---

Originally posted by rpc86

Hi guys,

I'm having trouble on my query and I could'nt find the
error. I'm using SQL Server 2005.

Here is my code:

SELECT Min(mainSA.State) as State, 
org.ObjectRetrievalGroupKey as [Queue Id],
MIN(org.ObjectRetrievalGroupDesc) as [Queue Description],
COUNT(DISTINCT innersa.SubjectAlertViolationKey) as NoViolations,
COUNT(DISTINCT innersa.ViolationSubjectKey) as NoUniqueCards

FROM ObjectRetrievalGroup org   
INNER JOIN SubjectAlert mainSA   
ON mainSA.SubjectAlertKey = org.ObjectRetrievalGroupKey

JOIN MemberGroupMember mgm ON mgm.MemberKey = org.MemberKey
INNER JOIN SubjectType st   
ON st.SubjectTypeKey = mainSA.SubjectTypeKey

LEFT JOIN 
(
SELECT v.SubjectAlertKey, v.SubjectAlertViolationKey, v.ViolationSubjectKey, v.RuleKey, v.ViolationSubjectTypeKey
FROM SubjectAlertViolation v JOIN SubjectAlert a  
ON a.SubjectAlertKey = v.SubjectAlertKey

WHERE ((Convert(Char(10),a.RunDate,101) >=  '09/02/2009' AND Convert(Char(10),a.RunDate,101) < '09/02/2009') OR a.RunDate IS NULL)
                 AND (a.AlertTypeId IN ('RTLR','ELEC','PAPR','COLA','TRAN','TRANAUTH') OR a.AlertTypeId IS NULL)
				 AND a.MemberKey IN (select MemberKey from MemberGroupMember WHERE mgm.MemberGroupKey = 1)
) as innersa 

ON innersa.RuleKey = org.ObjectRetrievalGroupKey AND innersa.ViolationSubjectTypeKey = mainSA.SubjectAlertKey

LEFT JOIN SubjectAlert a WITH (NOLOCK) ON a.SubjectAlertKey = innersa.SubjectAlertKey
WHERE st.SubjectTypeId = 'R'
AND mgm.MemberGroupKey =2
AND (a.MemberKey IN (select MemberKey from MemberGroupMember WHERE mgm.MemberGroupKey = 1) OR a.MemberKey IS null)

GROUP BY org.ObjectRetrievalGroupKey, org.MemberKey
HAVING ((Min(mainSA.State) = 'A' ) OR (Min(mainSA.State) <> 'A') AND COUNT(DISTINCT innersa.SubjectAlertViolationKey) > 0)

...and these are the errors:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "mainSA.SubjectAlertKey" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "mgm.MemberGroupKey" could not be bound.

The SubjectAlertKey is present in the SubjectAlert Table as well
as the MemberGroupKey is in MemberGroupMember Table.


Please help.

Thank you

---