sql - Check if record exists in subquery -

i have following 3 tables

source

id | name         | siteid ---+--------------+--------- 1  | source 1     | 1 2  | source 2     | 1 3  | source 3     | 2 4  | source 4     | 2 

sourceaccount

sourceid | accountid ---------+---------- 1        | 1 2        | 1 3        | 1 4        | 1 

sourceuser

sourceid | userid ---------+---------- 1        | 1 3        | 1 

i'm trying build query thew following parameters

• siteid (1)
• accountid (1)
• userid (1)

with parameters query should return this

sourceid | name         | access ---------+--------------+--------- 1        | source 1     | 1 2        | source 2     | 0 

access column being boolean (bit)

so in essence query should return sources (id , name) part of given accountid given siteid along boolean telling me user has access or not.

any idea on how proceed?

thanks

edit: record here query came not work:

 select s.[id], s.[name]       ,(if exists (select 1 [dbo].[sourceuser] su su.sourceid = s.[id] , su.userid = 1)             select cast(1 bit)         else             select cast(0 bit))   [dbo].[source] s   inner join [dbo].[sourceaccount] sa on sa.sourceid = s.id   sa.accountid = 1   , s.siteid = 1 

might worth mentionning i'm running on sql azure , sql server 2012

edit 2: relations

      siteaccount - site - siteuser - user       |                 \               | account - sourceaccount - source - sourceuser 

try this:

select s.[id],        s.[name],        case when exists (select 1 [dbo].[sourceuser] su su.sourceid = s.[id] , su.userid = 1)             cast(1 bit)             else cast(0 bit)        end access  [dbo].[source] s  inner join [dbo].[sourceaccount] sa on sa.sourceid = s.id  sa.accountid = 1 , s.siteid = 1