In some cases, the join columns from two tables have the same name. Then you must add a qualification to the column names to distinguish one name from the other.
How It's Done
The EMPLOYEE table does not carry the department name, and the DEPARTMENT table does not carry the employee ID. If you want to see the names of the departments that the employees are associated with, you need to join the EMPLOYEE and DEPARTMENT tables. These two tables have a common column, DEPT_ID.
Because the column DEPT_ID has the same name in each table, you need to qualify each common column name with the name of its table. Enter:
select emp_id, department.dept_id, dept_name
from department, employee
where department.dept_id = employee.dept_id;
This statement specifies that the rows to be retrieved and joined from the EMPLOYEE and DEPARTMENT tables are those that have matching department IDs. If a department ID is present in the DEPARTMENT table but not in the EMPLOYEE table (as when a department has no employees), that row will not be returned.
The result looks like this:
EMP_ID DEPT_ID DEPT_NAME ------ ------- --------- 2299 4600 MAINTENANCE 3411 5200 CORPORATE MARKETING 4773 3510 APPRAISAL - USED CARS 2010 2210 SALES - NEW CARS 3338 1120 PURCHASING - SERVICE 2246 1100 PURCHASING - USED CARS 1034 4600 MAINTENANCE 2424 4600 MAINTENANCE 3767 2200 SALES - USED CARS 2898 1120 PURCHASING - SERVICE 3449 5000 CORPORATE ACCOUNTING 3082 3510 APPRAISAL - USED CARS 3341 3530 APPRAISAL - SERVICE 3199 4600 MAINTENANCE 4660 2200 SALES - USED CARS 2209 3530 APPRAISAL - SERVICE 2894 5200 CORPORATE MARKETING 4001 2210 SALES - NEW CARS 5090 2210 SALES - NEW CARS 1765 1110 PURCHASING - NEW CARS 4456 4600 MAINTENANCE 2145 5200 CORPORATE MARKETING 3991 2210 SALES - NEW CARS 3778 5100 BILLING 4358 5200 CORPORATE MARKETING 4962 2210 SALES - NEW CARS 2180 2200 SALES - USED CARS 2106 1110 PURCHASING - NEW CARS 3222 4500 HUMAN RESOURCES 4002 6200 CORPORATE ADMINISTRATION 2437 4600 MAINTENANCE 2096 4600 MAINTENANCE 2004 1120 PURCHASING - SERVICE 5103 5000 CORPORATE ACCOUNTING 5008 1100 PURCHASING - USED CARS 4321 6200 CORPORATE ADMINISTRATION 2598 5100 BILLING 3764 2210 SALES - NEW CARS 2461 6200 CORPORATE ADMINISTRATION 2448 2200 SALES - USED CARS 1003 6200 CORPORATE ADMINISTRATION 1234 6200 CORPORATE ADMINISTRATION 2466 5000 CORPORATE ACCOUNTING 4027 2210 SALES - NEW CARS 2174 4500 HUMAN RESOURCES 2781 5200 CORPORATE MARKETING 3704 2200 SALES - USED CARS 4008 2210 SALES - NEW CARS 3841 6200 CORPORATE ADMINISTRATION 3433 4600 MAINTENANCE 3288 4600 MAINTENANCE 4703 1100 PURCHASING - USED CARS 3294 1120 PURCHASING - SERVICE 3118 4500 HUMAN RESOURCES 3769 3520 APPRAISAL NEW CARS 55 rows processed
Does it matter which of the two department ID columns you choose to display?
No. The values in each of the two matching columns is the same.
|
Copyright © 2014 CA.
All rights reserved.
|
|