title: Check if record exists in subquery
body: <p>I have the following 3 tables</p>
  
  <p><strong>Source</strong></p>
  
  <pre><code>Id | Name         | SiteId
  ---+--------------+---------
  1  | Source 1     | 1
  2  | Source 2     | 1
  3  | Source 3     | 2
  4  | Source 4     | 2
  </code></pre>
  
  <p><strong>SourceAccount</strong></p>
  
  <pre><code>SourceId | AccountId
  ---------+----------
  1        | 1
  2        | 1
  3        | 1
  4        | 1
  </code></pre>
  
  <p><strong>SourceUser</strong></p>
  
  <pre><code>SourceId | UserId
  ---------+----------
  1        | 1
  3        | 1
  </code></pre>
  
  <p>I'm trying to build up a query with thew following parameters</p>
  
  <ul>
  <li>SiteId (1)</li>
  <li>AccountId (1)</li>
  <li>UserId (1)</li>
  </ul>
  
  <p>With those parameters the query should return something like this</p>
  
  <pre><code>SourceId | Name         | Access
  ---------+--------------+---------
  1        | Source 1     | 1
  2        | Source 2     | 0
  </code></pre>
  
  <p>Access column being a boolean (bit)</p>
  
  <p>So in essence the query should return all the sources (Id and Name) that are part of a given AccountId for a given SiteId along with a boolean telling me is a user has access to it or not.</p>
  
  <p>Any idea on how to proceed?</p>
  
  <p>Thanks</p>
  
  <p>EDIT:
  For the record here is the query I came up with but does not work:</p>
  
  <pre><code> SELECT s.[Id], s.[Name]
        ,(IF EXISTS (SELECT 1 FROM [dbo].[SourceUser] su WHERE su.SourceId = s.[Id] AND su.UserId = 1)
              SELECT CAST(1 AS BIT)
          ELSE
              SELECT CAST(0 AS BIT))
    FROM [dbo].[Source] s
    INNER JOIN [dbo].[SourceAccount] sa ON sa.SourceId = s.Id
    WHERE sa.AccountId = 1
    AND s.SiteId = 1
  </code></pre>
  
  <p>Might also be worth mentionning that I'm running this on SQL Azure and SQL Server 2012</p>
  
  <p>EDIT 2: Relations</p>
  
  <pre><code>      SiteAccount - Site - SiteUser - User
        |                 \               |
  Account - SourceAccount - Source - SourceUser
  </code></pre>