Also display the model of computer allocated to the employee and the manager, how to do thi-CodePudding

Display id, name, manager id and manager name of those employees who are allocated a computer and whose manager is also allocated a computer. Also display the model of computer allocated to the employee and the manager. expected output: ID ENAME MGRID MGRNAME E_MODEL M_MODEL 5 Ayaz Mohammad 1 James Potter Edge Vostro

CodePudding user response：

select
    e.id, e.ename, e.mgrid, m.mgrname, e.e_model, m.m_model
from (
    select e.id, e.ename, e.manager mgrid, c.model e_model
    from employee e, computer c
    where e.compid = c.compid
) e, (
    select e.id mgrid, e.ename mgrname, c.model m_model
    from employee e, computer c
    where e.compid = c.compid
) m
where e.mgrid = m.mgrid

CodePudding user response：

You should consider attempting to do your own homework


CREATE TABLE computers (serial_number,  manufacturer, model) AS
SELECT 'D123',  'Dell', 'laptop' FROM DUAL UNION ALL
SELECT 'D124',  'Dell', 'laptop' FROM DUAL UNION ALL
SELECT 'A1424',  'Apple', 'laptop' FROM DUAL UNION ALL
SELECT 'A1425',  'Apple', 'laptop' FROM DUAL UNION ALL
SELECT 'C1725',  'compaq', 'tower' FROM DUAL UNION ALL
SELECT 'C1726',  'compaq', 'tower' FROM DUAL UNION ALL
SELECT 'C1727',  'compaq', 'tower' FROM DUAL;


CREATE TABLE employees (employee_id, manager_id, first_name, last_name, serial_number) AS
SELECT 1, NULL, 'Alice', 'Abbot', 'D123' FROM DUAL UNION ALL
SELECT 2, 1, 'Beryl', 'Baron','D124' FROM DUAL UNION ALL
SELECT 3, 1, 'Carol', 'Chase','A1424' FROM DUAL UNION ALL
SELECT 4, 2, 'Debra', 'Doris','A1425' FROM DUAL UNION ALL
SELECT 5, 3, 'Emily', 'Evans','C1725' FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', 'Frank','C1726' FROM DUAL UNION ALL
SELECT 7, 6, 'Gemma', 'Grace','C1727' FROM DUAL;


 select
    EMP.EMPLOYEE_ID,
    EMP.FIRST_NAME,
    EMP.LAST_NAME,
    EMP.MANAGER_ID,
    M.FIRST_NAME, 
    M.LAST_NAME,
    EMP.serial_number,
    C.manufacturer,
    C.model
      from
    employees emp
    JOIN computers c ON emp.serial_number = c.serial_number
    LEFT OUTER JOIN employees m ON emp.MANAGER_ID = m.EMPLOYEE_ID
   ORDER BY EMP.EMPLOYEE_ID;

EMPLOYEE_ID    FIRST_NAME    LAST_NAME    MANAGER_ID    FIRST_NAME    LAST_NAME    SERIAL_NUMBER    MANUFACTURER    MODEL
1    Alice    Abbot     -      -      -     D123    Dell    laptop
2    Beryl    Baron    1    Alice    Abbot    D124    Dell    laptop
3    Carol    Chase    1    Alice    Abbot    A1424    Apple    laptop
4    Debra    Doris    2    Beryl    Baron    A1425    Apple    laptop
5    Emily    Evans    3    Carol    Chase    C1725    compaq    tower
6    Fiona    Frank    3    Carol    Chase    C1726    compaq    tower
7    Gemma    Grace    6    Fiona    Frank    C1727    compaq    tower