query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-11-11 : 07:22:46

I have a table

menu

with a menuname

and

a table user menu
with menuname,userid

and a table
useraccess
id
user

how can i return a query that would show each menu along the top
and the list of users and a 0 or 1 wether they have a record in usermenu with their userid

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-11 : 07:32:33

select a.menuname,b.userid,case when c.UserID is null then 0 else 1 end
from
menu a
join usermenu b on a.menuname=b.menuname
left join useraccess c on c.user=b.userid

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-11-11 : 08:09:23

thanks.. not sure if this is possible but anyway to sort it so i see the menunames and then the list of userid's that exist for that menu in usermenu

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-11 : 08:28:37

select a.menuname,b.userid,case when c.UserID is null then 0 else 1 end
from
menu a
join usermenu b on a.menuname=b.menuname
left join useraccess c on c.user=b.userid
order by a.menuname,b.userid

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-11-18 : 03:03:27

having a hard time working with this -
anyway to get like the users as rows and menu items as collumns with 1 or 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources