CODEWITHSUNDEEP
sqldatabaseoracle-database
Abhinav
Abhinav

Reputation: 1027

sql: retrieve information for multiple rows in one go

I want to fetch rows based on values from one query. I know that this can be done using sub query but I want to use a single query which will execute and fetch detail using one query based on a given condition and then if detail contains some value then using other value in detail, I want to fetch details using outer query.

For example: Say an employee can belong to multiple departments (a,b,c,d, etc.) in a financial year and in a financial year employee can belong to one or more department. I want to retrieve details of all employees for latest financial year and belonging to department c.

EMPLOYEE_DEPARTMENTS is a table which contains information like a foreign key(combination of employee id and financial year) and department ID. foreign key is something like 123_1718 where 1718 shown financial year and 123 is employee id. And if we retrieve an employee which belongs to department c then I want to retrieve other details of employee from other tables.

I know this can be done by writing multiple SQLs to find out employee belonging department c for all financial year and then for every employee check whether employee belong to department c in a financial year and if not then remove it from list. But is there a way I can get write pl/sql or anything through which I can retrieve all details in one go.

Example:

EMPLOYEE_DEPARTMENTS
————————————————————
ID_EMPFY        ID_DEPARTMENT
1               3
2               1
2               2
1               3
4               1
3               2
4               3
4               2


EMPLOYEE
ID  NAME    ID_DEPARTMENT       CITY

DEPARTMENT
ID  NAME
1   a
2   b
3   c

EMPLOYEE_FINANCIALYEAR
ID  ID_EMPLOYEE    FINYEAR
1   123             1718
2   123             1516
3   456             1718
4   234             1718

I have to retrieve employees belonging to department c and FINYEAR which can be any last financial year in which employee was part of company. It means for employee I have to find his last year in company (which can be done using table EMPLOYEE_FINANCIALYEAR) and then check if last year was having department as c and if it has then using ID_EMPFY from EMPLOYEE_DEPARTMENTS, get EMPLOYEE_D from EMPLOYEE_FINANCIALYEAR and then using ID_EMPLOYEE retrieve all details of employee.

Thanks in advance for your help.

Upvotes: 0

Views: 69

Answers (1)

Mehdi Ghasri
Mehdi Ghasri

Reputation: 498

You can use this Query:

WITH LAST_EMPLOYEE_FINANCIAL_YEAR AS
(
SELECT *
FROM   (SELECT T.*,
               ROW_NUMBER() OVER(PARTITION BY T.ID_EMPLOYEE ORDER BY T.FINYEAR DESC) ROW_NUM
        FROM   EMPLOYEE_FINANCIALYEAR T
       )
WHERE  ROW_NUM = 1
)
SELECT *
FROM LAST_EMPLOYEE_FINANCIAL_YEAR EF
INNER JOIN EMPLOYEE_DEPARTMENTS ED ON ED.ID_EMPFY = EF.ID
INNER JOIN DEPARTMENT D ON D.ID = ED.ID_DEPARTMENT
      AND D.NAME = 'C';

Upvotes: 1

Related Questions