ASP.NET Membership query

[billmack30]

Given the table structures and table data below can anyone help me on writing the query to get the results as below

The ultimate goal of this query is so I can have a gridview loaded and an administrator can see a list of users with email and whether they are active, an administrator, and/or a dataentry person



QUERY RESULTS DESIRED:

Username Email IsApproved IsAdmin IsDataEntry
Joe Smith joe@joe.com 1 1 1
Bob Jones bob@bob.com 1 0 1

Table Structure

MEMBERSHIP
userid
email
isapproved

USERS
userid
username

ROLES
roleid
rolename

USERSINROLES
roleid
userid


Tables

MEMBERSHIP
userid email isapproved
1 joe@joe.com 1
2 bob@bob.com 1

USERS
userid username
1 Joe Smith
2 Bob Jones

ROLES
roleid rolename
3 Admin
4 DataEntry

USERSINROLES
userid roleid
1 3
1 4
2 4

 

[RyanEK]

Hi,

This should work...

select u.Username,
isnull((select m.isapproved from Membership m where m.userid = u.userid),0) as IsApproved,
isnull((select 1 from Roles r join Userinroles ur on ur.userid = u.userid 
 and ur.roleid = r.roleid and r.rolename = 'Admin'),0) as IsAdmin,
isnull((select 1 from Roles r join Userinroles ur on ur.userid = u.userid 
 and ur.roleid = r.roleid and r.rolename = 'DataEntry'),0) as IsDataEntry  
from Users u

Ryan