title: Subsonic 2.2 SqlQuery with Inner Join and Where
body: <p>I am trying to convert the following SQL into Subsonic syntax using the SqlQuery functionality:</p>
  
  <pre><code>SELECT DISTINCT * FROM FamilyMemberTeamRole FMTR 
  INNER JOIN TeamRole TR ON FMTR.TeamRoleId = TR.TeamRoleId
  INNER JOIN Team T ON T.TeamId = TR.TeamId
  LEFT JOIN FamilyMemberClassHistory FMCH ON FMCH.FamilyMemberClassHistoryId = FMTR.FamilyMemberClassHistoryId
  LEFT JOIN CBSClass CG ON CG.CBSClassGroupId = FMCH.CBSClassGroupId
  LEFT JOIN CBSClassSession CS ON CG.CBSClassGroupId = CS.CBSClassGroupId 
  AND (CS.ClassStartDate &lt;= FMTR.EndDate or FMTR.EndDate IS NULL)
  AND (CS.IsHistory = 0 OR CS.IsHistory = NULL)
  WHERE FMTR.FamilyMemberId = @FamilyMemberId
  </code></pre>
  
  <p>I came up with this however something is wrong with my syntax on the last left join as I do not know how to compare values from within the SqlQuery to themselves.</p>
  
  <pre><code>SqlQuery sql = new Select().From(FamilyMemberTeamRole.Schema.TableName)
  .InnerJoin(TeamRole.TeamRoleIdColumn, FamilyMemberTeamRole.TeamRoleIdColumn)
  .InnerJoin(Team.TeamIdColumn, TeamRole.TeamIdColumn)
  .LeftOuterJoin(FamilyMemberClassHistory.FamilyMemberClassHistoryIdColumn, FamilyMemberTeamRole.FamilyMemberClassHistoryIdColumn)
  .LeftOuterJoin(CBSClass.CBSClassGroupIdColumn, FamilyMemberClassHistory.CBSClassGroupIdColumn)
  .LeftOuterJoin(CBSClassSession.CBSClassGroupIdColumn, CBSClass.CBSClassGroupIdColumn)
  .AndExpression(CBSClassSession.Columns.ClassStartDate).IsLessThanOrEqualTo(FamilyMemberTeamRole.Columns.EndDate)
  .Or(FamilyMemberTeamRole.Columns.EndDate).IsNull().CloseExpression()
  .AndExpression(CBSClassSession.Columns.IsHistory).IsEqualTo(false)
  .Or(CBSClassSession.Columns.IsHistory).IsNull().CloseExpression()
  .Where(FamilyMemberTeamRole.Columns.FamilyMemberId).IsEqualTo(this.FamilyMemberId)
  .Distinct();
  </code></pre>