title: SQL - Complex Query LEFT JOIN with WHERE
body: <p>The query below is a simplified version of the query where i am having errors, this smaller query shown below previously had a where clause which was effectively nullifying my left joins.</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT FC_Name, COUNT(F.Findings_ID) AS 'No_of_Findings'
  FROM FindingCategories AS FC
  LEFT OUTER JOIN Findingsubcategories AS FSC ON FSC.FC_ID = FC.FC_ID
  LEFT OUTER JOIN Findings AS F ON F.FSC_ID = FSC.FSC_ID AND F.AU_ID = @Audit_ID
  LEFT OUTER JOIN Audit AS A ON A.Audit_ID = F.AU_ID 
  GROUP BY FC_Name
  </code></pre>
  
  <p>Now - unfortunately i cant use this query for what i'm trying to, accomplish:
  I need to grab the details in my database for the current selection, and the values from the previous year. Shown below:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT A.Audit_ID, c.FC_Name, COUNT(c.FC_Name) AS 'No_of_Findings'
  FROM Audit AS A
    LEFT OUTER JOIN Findings F on A.Audit_ID = f.AU_ID
    LEFT OUTER JOIN FindingCategories C on C.FC_ID = F.Findings_category_ID
    LEFT OUTER JOIN GovernmentAgencies GA on GA.GA_ID = A.GA_ID
    LEFT OUTER JOIN AuditType AT on AT.AuditType_ID = a.AuditType_ID 
  WHERE A.Audit_Year= @Year AND (@Agency IS NULL OR ga.GA_LegalName = @Agency)
                             AND (@AuditType is null or @AuditType = AT.AuditType_Category)
                             AND (@Audit_ID is null or @Audit_ID  = a.Audit_ID)
  GROUP BY A.Audit_Year, c.FC_Name, A.Audit_ID
  
  SELECT a.Audit_ID, c.FC_Name, COUNT(c.FC_Name) AS 'No_of_Findings'
  FROM Audit AS A
    LEFT OUTER JOIN Findings F on a.Audit_ID = f.AU_ID
    LEFT OUTER JOIN FindingCategories C on C.FC_ID = F.Findings_category_ID
    LEFT OUTER JOIN GovernmentAgencies GA on GA.GA_ID = a.GA_ID
    LEFT OUTER JOIN AuditType AT on AT.AuditType_ID = a.AuditType_ID
  WHERE a.Audit_Year= '@Year-1 AND (@Agency IS NULL OR ga.GA_LegalName = @Agency)
                             AND (@AuditType is null or @AuditType = AT.AuditType_Category)
  GROUP BY a.Audit_Year, c.FC_Name, a.Audit_ID
  </code></pre>
  
  <p>Again, as per my first query i realize my WHERE turns my left join into an inner join, but I've tried a few different ways to remedy this to no avail. How do i change this to remedy the issue?</p>
  
  <p>Sample Data:
  Table1/Table2</p>
  
  <p><a href="https://i.stack.imgur.com/0rvHM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0rvHM.png" alt="enter image description here"></a></p>
  
  <p>What Table 1 Should Look Like - using the simpler query:</p>
  
  <p><a href="https://i.stack.imgur.com/S9PAF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/S9PAF.png" alt="enter image description here"></a></p>