SQLTeam.com | Weblogs | Forums

Help on a Query


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

AppID ContactID
1 1
1 2
1 3
2 4
3 1

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

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


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


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, 
	   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


Thank you so much - that worked great!


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