I have this structure of tables:
CREATE TABLE Users
([UserId] int,
[IdDepartment] int);
INSERT INTO Users
([UserId], [IdDepartment])
VALUES
(1, 5),
(2, 0),
(3, -1),
(4, 0),
(5, -1),
(6, 0);
CREATE TABLE Department
([IdDepartment] int, [Name] varchar(23), [IdUser] int);
INSERT INTO Department
([IdDepartment], [Name], [IdUser])
VALUES
(1, 'Sales', 3),
(2, 'Finance', null ),
(3, 'Accounting' , 5),
(4, 'IT' ,3),
(5, 'Secretary',null),
(6, 'Sport',3);
I want to get a query with this results: In the Users table if the IdDepartment is 0 ist means that the user is an admin so he can see all the departments. If the user has a -1 in the idpartment it means that the user can access to limited departments, so in this case I do a inner join to the Department table to get the list of this departments. The last case is if the user has a number for the idDepartament in the user table diferent to 0 and diferent to -1 it means that the user can access only to this department.
I tried to do something like that, but it is not well structured:
select
case idDepartment
when 0 then (select Name from Department)
when -1 then (select Name from Department where IdUser = 3)
else (select Name from Department
inner join Users on Department.idDepartment = Users.Department
where Users.UserId = 3)
end
from
Department
where
IdUser = 3
How can I do this? thanks.
I add an example for what I want to get:
-For the user that has the userid (1) --> Department Name --------------- Secretary -For the user that has the userid (2) --> Department Name --------------- Sales Finance Accounting IT Secretary Sport -For the user that has the userid (3) --> Department Name --------------- Sales IT
Aucun commentaire:
Enregistrer un commentaire