Help on a Query

Transact-SQL
1

I need to grab all the AppNames from TableB (they are all unique), and when I pass my paramater (ContactID), list if that Contact has Access based on the relationship of ContactID in TableA.

I can do a simple select on TableB but then I'm stuck without making more calls to TableA. Is there a way I can do this in one statement?

@ContactID = 1

TableA:
AppID ContactID
1 1
1 2
1 3
2 4
3 1

TableB
AppID AppName
1 Application A
2 Application B
3 Application C
4 Application D
5 Application E

Desired Output:
AppName HasAccess
Application A true
Application B false
Application C true
Application D false
Application E false

2 
declare @ContactID  int = 1
select a.AppName , a.HasAccess
 from TableA a
join TableB b on a.AppID  = b.AppID	
where a.ContactId = @ContactID
3

Thank you, but this does not work. HasAccess is not a column name, I just need to list all the application names from TableB, then in the relationship table(TableA), if ContactID = 1, then have this new column "HasAccess" as true

4

oh duh sorry :frowning:

declare @ContactID  int = 1

create table #appaccess(appid int,	contactid int)

insert into #appaccess
select 1,	1 union
select 1,	2 union
select 1,	3 union
select 2,	4 union
select 3,	1

create table #apps(appid int, appname varchar(50))

insert into #apps
select 1,'Application A' union
select 2,'Application B' union
select 3,'Application C' union
select 4,'Application D' union
select 5,'Application E' 


select appname, 
       case 
	   when aa.appid is null then 'False'
	   else 'true' 
	   end as HasAccess
from #apps a
left join #appaccess aa on a.appid = aa.appid
and contactid = @ContactID
 

 drop table #appaccess
 drop table #apps
1 Like
5

Thank you so much - that worked great!

6

If you were given a working answer, please mark THAT post as the answer... Not your "thank you"...