egovernment
asked on
SQL Statement in Oracle & Access
If I have these tables
Table_A
======
For example
Table_B
======
For example
Table_C
======
For example
Table_D
======
For example
What is the SQL Statement to display the following: -
In oracle & Access
Table_A
======
Emp_Id
Emp_Name
Emp_Grade_Id
For example
Emp_Id Emp_Name Emp_Grade_Id
333 John Smith 0007
666 Mikel Josef 008
Table_B
======
Emp_Grade_Id
Emp_Grade_Name
For example
Emp_Grade_Id Emp_Grade_Name
0007 Manager
008 System Analysis
Table_C
======
Emp_Id
ALW_DED_Id
Amount
For example
Emp_Id ALW_DED_Id Amount
333 1000 500
333 1001 50
333 1002 30
333 1003 15
Table_D
======
ALW_DED_Id
ALW_DED_NAME
For example
ALW_DED_Id ALW_DED_NAME
1000 Basic
1001 Accomadation
1002 Electrisity
1003 Water
What is the SQL Statement to display the following: -
Emp_Id Emp_Name Emp_Grade_Name Basic Accomadation Elec Water
In oracle & Access
Is there a maximum number of ALW_DED_Id entries for employees in table_C?
ASKER
Is there a maximum number of ALW_DED_Id entries for employees in table_C?
4
4
What version of Access are you using?
Try this for Oracle:
Try this for Oracle:
select a.emp_id, a.emp_name,
b.emp_grade_name,
max(case when c.alw_ded_id = '1000' then alw_ded_name end) basic,
max(case when c.alw_ded_id = '1001' then alw_ded_name end) accomadation,
max(case when c.alw_ded_id = '1002' then alw_ded_name end) electricity,
max(case when c.alw_ded_id = '1001' then alw_ded_name end) water
from table_a a,
table_b b,
table_c c,
table_d d
where a.emp_grade_id=b.emp_grade_id and
a.emp_id=c.emp_id and
c.alw_ded_id=d.alw_ded_id
group by a.emp_id, a.emp_name, b.emp_grade_name
/
ASKER
What version of Access are you using?
Access 2003
Access 2003
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.