Accessing Multiple Tables › Qualifying a Table Name

Qualifying a Table Name

In some cases, the tables are in different schemas. Then you must add a qualification to the table name to specify which schema the table is associated with.

How It's Done

The EMPLOYEE table doesn't contain project id and description information, and the PROJECT table doesn't carry the project leader's first and last name. If you want to see the projects and their respective project leaders, you must join the EMPLOYEE and PROJECT tables using the PROJ_LEADER_ID and EMP_ID columns. However, the EMPLOYEE table is assigned to the DEMOEMPL schema and the PROJECT table is assigned to the DEMOPROJ schema. To access data from both schemas, you must qualify the table names with the schema name. Enter:

select emp_id, emp_lname, emp_fname, proj_desc from
       demoempl.employee, demoproj.project
       where emp_id=proj_leader_id;

EMP_ID  EMP_LNAME             EMP_FNAME      PROJ_DESC
------  ---------             ---------      ---------
  3411  Williams              Catherine      TV ads - WTVK
  3411  Williams              Catherine      New brand research
  2894  Griffin               William        Consumer study
  4358  Robinson              Judith         Service study
  2466  Bennett               Patricia       Systems analysis

5 rows processed