a tricky query - 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 2000 Forums

Transact-SQL (2000)

a tricky query

Author

Topic

vgurgov
Starting Member

12 Posts

Posted - 2005-05-23 : 14:44:23

Hi,

I have these tables:

[Facility]
facil_id (int)

[Manager]
mgr_id (int)
is_admin (0|1)
username (varchar(20))

[FacilityManagers]
facil_id (int)
mgr_id (int)

The 'FacilityManagers' table has the relationship between the 'Facility' and 'Manager' tables.

My problem is that when a manager is logging in I need to display all facilities that he has access to. If is_admin is true, then he can see ALL facilities, otherwise only the ones that are in the relationship table.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-23 : 15:25:50

select f.*
from Facility f
cross join Manager m
left join FacilityManagers fm
on fm.mgr_id = m.mgr_id
and fm.facil_id = f.facil_id
where m.is_admin = 1
or fm.mgr_id is not null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vgurgov
Starting Member

12 Posts

Posted - 2005-05-23 : 17:56:24

It worked. Thank you nr!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources