mercredi 30 mars 2016

How can I do select case to this query?

SQL FIDDLE DEMO HERE

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