locked
how to do this query RRS feed

  • Question

  • User569149469 posted
    -- Example case create statement:
    CREATE TABLE employees (
      id INTEGER NOT NULL PRIMARY KEY,
      managerId INTEGER REFERENCES employees(id), 
      name VARCHAR(30) NOT NULL
    );
    
    INSERT INTO employees(id, managerId, name) VALUES(1, NULL, 'John');
    INSERT INTO employees(id, managerId, name) VALUES(2, 1, 'Mike');
    
    -- Expected output (in any order):
    -- name
    -- ----
    -- Mike
    
    -- Explanation:
    -- In this example.
    -- John is Mike's manager. Mike does not manage anyone.
    -- Mike is the only employee who does not manage anyone.
    Saturday, August 18, 2018 6:01 AM

All replies

  • User-369506445 posted

    hi

    you can use a <g class="gr_ gr_50 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="50" data-gr-id="50">self join</g> below like :

    select e1.name as manager , e2.name as employee from employees e1
             join employees e2 on e1.id = e2.managerId  

    result

    manager	 employee
    John	  Mike
    

    Saturday, August 18, 2018 9:22 AM
  • User347430248 posted

    Hi phmaspnet,

    You had mentioned thatm"John is Mike's manager. Mike does not manage anyone. Mike is the only employee who does not manage anyone."

    Do you mean Mike does not have any department or responsibility to manage and you want to find it?

    You only post one table.

    I suggest you to post the other related tables, So that on the bases of that tables we can find the manager who does not have any responsibility to manage.

    If you mean that Mike leave the job then you may try to add one more field in table which stores the information who left the job.

    You can try to provide detailed information about your requirement.

    It may help us to understand the issue in better way.

    Regards

    Deepak

    Monday, August 20, 2018 6:56 AM
  • User77042963 posted

    You can play with recursive cte to work with this type of question. A sample:

    -- Example case create statement:
    CREATE TABLE employees (
      id INTEGER NOT NULL PRIMARY KEY,
      managerId INTEGER REFERENCES employees(id), 
      name VARCHAR(30) NOT NULL
    );
    
    INSERT INTO employees(id, managerId, name) VALUES(1, NULL, 'John');
    INSERT INTO employees(id, managerId, name) VALUES(2, 1, 'Mike');
    
    ;with rcte as (
    Select id, managerId, Cast(name as varchar(2000)) name ,0 as lvl from employees where managerId  is null
    Union all
    select e1.id , e1.managerId, Cast(e2.name+'>'+e1.name as varchar(2000)) as employee, 1+lvl lvl from employees e1
     join rcte e2 on e2.id = e1.managerId  
      )
    
     Select * from rcte
    
      
    Drop table employees
    

    Monday, August 20, 2018 3:32 PM