We help IT Professionals succeed at work.

SQL Statement in Oracle & Access

If I have these tables

Table_A
======
Emp_Id
Emp_Name
Emp_Grade_Id

Open in new window


For example
Emp_Id        Emp_Name         Emp_Grade_Id
333              John Smith          0007
666              Mikel Josef          008

Open in new window


Table_B
======
Emp_Grade_Id
Emp_Grade_Name

Open in new window


For example
Emp_Grade_Id          Emp_Grade_Name
0007                         Manager
008                           System Analysis

Open in new window


Table_C
======
Emp_Id
ALW_DED_Id
Amount

Open in new window


For example
Emp_Id                     ALW_DED_Id            Amount
333                           1000                        500
333                           1001                        50
333                           1002                        30
333                           1003                        15

Open in new window


Table_D
======
ALW_DED_Id
ALW_DED_NAME

Open in new window


For example
ALW_DED_Id            ALW_DED_NAME
1000                        Basic
1001                        Accomadation
1002                        Electrisity
1003                        Water

Open in new window


What is the SQL Statement to display the following: -
Emp_Id     Emp_Name      Emp_Grade_Name   Basic    Accomadation    Elec   Water

Open in new window


In oracle & Access
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Is there a maximum number of ALW_DED_Id entries for employees in table_C?

Author

Commented:
Is there a maximum number of ALW_DED_Id entries for employees in table_C?
4
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
What version of Access are you using?

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
/

Open in new window

Author

Commented:
What version of Access are you using?
Access 2003
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
First there is a typo in the SQL I posted above.

Change:        max(case when c.alw_ded_id = '1001' then alw_ded_name end) water
To:       max(case when c.alw_ded_id = '1003' then alw_ded_name end) water


Access doesn't appear to have a CASE statement in SQL but they have a SWITCH function that bascially does the same thing.

I don't have Access 2003 around but the following was tested in Access 2007 and should run fine in 2003.

SELECT table_a.Emp_Id, table_a.Emp_Name, table_b.Emp_Grade_Name, 
   max(Switch(  table_c.ALW_DED_Id="1000",alw_ded_name )) AS Basic,
   max(Switch(  table_c.ALW_DED_Id="1001",alw_ded_name )) AS Accomadation,
   max(Switch(  table_c.ALW_DED_Id="1002",alw_ded_name )) AS Electricity,
   max(Switch(  table_c.ALW_DED_Id="1003",alw_ded_name )) AS Water
FROM table_d INNER JOIN (table_c INNER JOIN (table_a INNER JOIN table_b ON table_a.Emp_Grade_Id = table_b.Emp_Grade_Id) ON table_c.Emp_Id = table_a.Emp_Id) ON table_d.ALW_DED_Id = table_c.ALW_DED_Id
group by  table_a.Emp_Id, table_a.Emp_Name, table_b.Emp_Grade_Name;

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.