SQL Procedure

Example 11: Joining Three Tables

Features:	FROM clause joined-table component WHERE clause
Table names:	Staff2 Schedule2 Superv2
Note:	For information about the input data tables that are used in the examples, see Tables Used in the Examples.

Features:

FROM clause

joined-table component

WHERE clause

Table names:

Staff2

Schedule2

Superv2

Note:

For information about the input data tables that are used in the examples, see Tables Used in the Examples.

Details

This example joins three tables and produces a report that contains columns from each table.

Staff2 Table

proc sql;
   title 'Staff2';
   select * from staff2;
   title;
quit;

proc sql;
   title 'Staff2';
   select * from staff2;
   title;
quit;

Schedule2 Table

proc sql;
   title 'Schedule2';
   select * from schedule2;
   title;
quit;

proc sql;
   title 'Schedule2';
   select * from schedule2;
   title;
quit;

Superv2 Table

proc sql;
   title 'Superv2';
   select * from superv2;
   title;
quit;

proc sql;
   title 'Superv2';
   select * from superv2;
   title;
quit;

Program

proc sql;
   title 'All Flights for Each Supervisor';
   select s.IdNum, Lname, City 'Hometown', Jobcat,
          Flight, Date
from schedule2 s, staff2 t, superv2 v
where s.idnum=t.idnum and t.idnum=v.supid;
quit;

proc sql;
   title 'All Flights for Each Supervisor';
   select s.IdNum, Lname, City 'Hometown', Jobcat,
          Flight, Date
from schedule2 s, staff2 t, superv2 v
where s.idnum=t.idnum and t.idnum=v.supid;
quit;

Program Description

Select the columns.The SELECT clause specifies the columns to select. IdNum is prefixed with a table alias because it appears in two tables.

proc sql;
   title 'All Flights for Each Supervisor';
   select s.IdNum, Lname, City 'Hometown', Jobcat,
          Flight, Date

Specify the tables to include in the join.The FROM clause lists the three tables for the join and assigns an alias to each table.

from schedule2 s, staff2 t, superv2 v

Specify the join criteria.The WHERE clause specifies the columns that join the tables. The Staff2 and Schedule2 tables each have an IdNum column, which enables a join on rows where these column values match in both tables. The Staff2 and Superv2 tables have the IdNum and SupId columns, which enable a join on rows where these column values match in both tables. The combination of these two conditions enables the three tables to be joined.

where s.idnum=t.idnum and t.idnum=v.supid;
quit;

Output: Joining Three Tables

All Flights for Each Supervisor

Last updated: September 16, 2026