title: How to left join with conditions in Toad Data Point Query Builder?
body: <p>I'm trying to build a query in Toad Data Point. I have a subquery that has a row number to identify the records I'm interested in. This subquery needs to be left joined onto the main table only when the row number is 1. Here's the query I'm trying to visualize:</p>
  
  <pre><code>SELECT distinct E.EMPLID, E.ACAD_CAREER
  FROM PS_STDNT_ENRL E
  LEFT JOIN (
    SELECT ACAD_CAREER, ROW_NUMBER() OVER (PARTITION BY ACAD_CAREER ORDER BY EFFDT DESC) as RN
    FROM PS_ACAD_CAR_TBL
  ) T on T.ACAD_CAREER = E.ACAD_CAREER and RN = 1
  </code></pre>
  
  <p>When I try to replicate this, the row number condition is placed in the global WHERE clause. This is not the intended functionality because it removes any records that don't have a match in the subquery effectively making it an inner join.</p>
  
  <p><a href="https://i.stack.imgur.com/J6CHr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/J6CHr.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/jVIac.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jVIac.png" alt="enter image description here"></a></p>
  
  <p>Here is the query it's generating:</p>
  
  <pre><code>SELECT DISTINCT E.EMPLID, E.ACAD_CAREER, T.RN
    FROM SYSADM.PS_STDNT_ENRL E
         LEFT OUTER JOIN
         (SELECT PS_ACAD_CAR_TBL.ACAD_CAREER,
                 ROW_NUMBER ()
                    OVER (PARTITION BY ACAD_CAREER ORDER BY EFFDT DESC)
                    AS RN
            FROM SYSADM.PS_ACAD_CAR_TBL PS_ACAD_CAR_TBL) T
            ON (E.ACAD_CAREER = T.ACAD_CAREER)
   WHERE (T.RN = 1)
  </code></pre>
  
  <p>Is there a way to get the query builder to place that row number condition on the left join instead of the global WHERE clause?</p>