Hi Rakesh,
This is the script for above DB Design...
CREATE TABLE StudiesMaster ( StudiesId int PRIMARY KEY, Name varchar(10) )
insert into StudiesMaster values(1, 'MS SQL') , (2, 'JAVA'), (3, 'ASP .NET')
CREATE TABLE UsersMaster ( UserId int PRIMARY KEY, Name varchar(10) )
insert into UsersMaster values(1, 'chandu') , (2, 'manu'), (3, 'sailu')
CREATE TABLE StudiesAccess (StudiesAccessId int IDENTITY , UserId int REFERENCES UsersMaster(UserId), StudiesId int REFERENCES StudiesMaster(StudiesId))
insert into StudiesAccess values(1, 1), ( 1, 3), ( 2, 1)
--1. What would be the query to see which study the user have access?
SELECT
U.Name as 'User name',
SM.Name AS 'Study Name'
FROM StudiesAccess SA INNER JOIN UsersMaster U
ON SA.UserId = U.UserId INNER JOIN StudiesMaster SM
ON SM.StudiesId = SA.StudiesId
--2. Which study the user have no access?
--this is top class... In second query if the the study is assigned to even one user, then it will not come up for rest of the users, any thoughts
SELECT
SM.Name AS 'Study Name'
FROM StudiesMaster SM
WHERE SM.StudiesId NOT IN (SELECT StudiesId FROM StudiesAccess)
--3. How to assign access to the study if the user do not have access to that study?
DECLARE @study int = 1, @user int = 2
IF NOT EXISTS (SELECT 1 FROM StudiesAccess WHERE StudiesId = @study AND UserId = @User)
INSERT StudiesAccess (StudiesId, UserId) VALUES(@study, @User)
SELECT * FROM StudiesAccess
DROP TABLE StudiesAccess
DROP TABLE UsersMaster
DROP TABLE StudiesMaster
In the above script [b]ASP .NET[/b] is assigned to only one user... That why the study name "ASP .NET" is not in the result ( As per Mahesh's solution)
>> [b]In second query if the study is assigned to even one user, then it will not come up for rest of the users[/b]
SELECT
SM.Name AS 'Study Name'
FROM StudiesMaster SM
WHERE SM.StudiesId NOT IN (SELECT StudiesId FROM StudiesAccess)
OUTPUT:
JAVA
Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif
Nerdanalysis, if this helps please login to Mark As Answer. | Alert Moderator