SQL & PL/SQL

Hi All,

I need to get all the employees for whom all of the balances are not attached for each element.

Element is attached to employees and balance is attached to elements. If any of the elements are not attached to all of the balances from the balance table, I need to get those employees.

Even though I kept outer join, only the matching rows are getting returned.

======================================================

Below is the script to create tables and insert data.

create table temp_emp (person_id number,employee_number number);

create table temp_element_entry (element_entry_id number,person_id number,element_name varchar2(500));

create table temp_element_balance_map (balance_entry_id number,element_entry_id number,balance_id varchar2(500));

create table temp_balance (balance_id number,balance_name varchar2(500));

Insert into TEMP_EMP (PERSON_ID,EMPLOYEE_NUMBER) values (1,10);

Insert into TEMP_EMP (PERSON_ID,EMPLOYEE_NUMBER) values (2,20);

Insert into TEMP_EMP (PERSON_ID,EMPLOYEE_NUMBER) values (3,30);

Insert into TEMP_ELEMENT_ENTRY (ELEMENT_ENTRY_ID,PERSON_ID,ELEMENT_NAME) values (1,1,'Basic Salary');

Insert into TEMP_ELEMENT_ENTRY (ELEMENT_ENTRY_ID,PERSON_ID,ELEMENT_NAME) values (2,2,'HRA');

Insert into TEMP_ELEMENT_ENTRY (ELEMENT_ENTRY_ID,PERSON_ID,ELEMENT_NAME) values (3,3,'LTA');

Insert into TEMP_ELEMENT_BALANCE_MAP (BALANCE_ENTRY_ID,ELEMENT_ENTRY_ID,BALANCE_ID) values (1,1,'1');

Insert into TEMP_ELEMENT_BALANCE_MAP (BALANCE_ENTRY_ID,ELEMENT_ENTRY_ID,BALANCE_ID) values (2,1,'2');

Insert into TEMP_ELEMENT_BALANCE_MAP (BALANCE_ENTRY_ID,ELEMENT_ENTRY_ID,BALANCE_ID) values (3,2,'1');

Insert into TEMP_ELEMENT_BALANCE_MAP (BALANCE_ENTRY_ID,ELEMENT_ENTRY_ID,BALANCE_ID) values (4,3,'2');

Insert into TEMP_BALANCE (BALANCE_ID,BALANCE_NAME) values (1,'Local Tax');

Insert into TEMP_BALANCE (BALANCE_ID,BALANCE_NAME) values (2,'State Tax');

======================================

The SQL query I bulit is below:

===============================

SELECT emp.employee_number,

       ele.element_name,

       ele.element_entry_id,

       bal.balance_name

  FROM temp_emp emp,

       temp_element_entry ele,

       temp_element_balance_map bal_entry,

       temp_balance bal

WHERE     emp.person_id = ele.person_id

       AND ele.element_entry_id = bal_entry.element_entry_id

       AND bal_entry.balance_id = bal.balance_id(+)

       AND ele.element_name =

              DECODE ('&&element_name',

                      'ALL', ele.element_name,

                      '&&element_name')

       AND bal.balance_name(+) =

              DECODE ('&&balance_name',

                      'ALL', bal.balance_name(+),

                      '&&balance_name')

order by 1 asc;

The Current output is below:

======================

The Expected output is below:

=======================

Can you please help me to achieve this?

Thanks,

Ramesh Selvam.