Problem With Join 1

[kshanes]

I'm querying against 3 tables to get a count by type and year. The problem is that my counts are off as there are values in dbo_t_goal.assignedTo that are not in table ConsolidateGroups.user. I was attempting to do a left join to pick up those values in dbo_t_goal.assignedTo, but I keep getting syntax errors. I've read a bunch of posts, but every time I attempt to mimic what the other posts are doing, I still get syntax errors. The query is below. Any help would be greatly appreciated.

SELECT COUNT(*) AS Total, 
             GC.goalType AS [Goal Cat], CGR.ACL,
             DATEPART("yyyy",G.startDate) AS [Year]
FROM   ((dbo_t_goal as G left JOIN ConsolidateGroups as CGR on G.assignedTo = CGR.user)
             Left Join dbo_t_Goal_Category as GC on G.fk_GoalCatId = GC.pk_GoalCatId),
where   LEN(GC.validSQL) > 1
AND      GC.active = 'Y'
AND     G.status = 'C'
and     G.deleted = 'n'
and GC.goalType <> 'bogus value'
GROUP BY GC.goalType, CGR.ACL, DATEPART("yyyy",G.startDate)
ORDER BY GC.goalType, DATEPART("yyyy",G.startDate);

 

[lupins46]

The problem is due to the selection on GC.
You need to do the selection first in a subquery and the join on the result of the selection. Remove the corresponding criteria from the main query.

..... Left Join (Select * from dbo_t_Goal_Category
where LEN(GC.validSQL) > 1
AND GC.active = 'Y'
and GC.goalType <> 'bogus value')as GC
on G.fk_GoalCatId = GC.pk_GoalCatId),