Multiple join types in one statement

 

Sometimes join more than two tables to produce the result that you want. If you want to return all the employees, their department names, and the projects they are responsible for, if any, join the EMPLOYEE table, the DEPARTMENT table, and the PROJECT table to get the information. The following example shows the query and the results:

  SELECT EMPNO, LASTNAME, DEPTNAME, PROJNO     FROM CORPDATA.EMPLOYEE INNER JOIN CORPDATA.DEPARTMENT           ON WORKDEPT = DEPTNO         LEFT OUTER JOIN CORPDATA.PROJECT           ON EMPNO = RESPEMP     WHERE LASTNAME > 'S'

EMPNO	LASTNAME	DEPTNAME	PROJNO
000020	THOMPSON	PLANNING	PL2100
000060	STERN	MANUFACTURING SYSTEMS	MA2110
000100	SPENSER	SOFTWARE SUPPORT	OP2010
000170	YOSHIMURA	MANUFACTURING SYSTEMS	-
000180	SCOUTTEN	MANUFACTURING SYSTEMS	-
000190	WALKER	MANUFACTURING SYSTEMS	-
000250	SMITH	ADMINISTRATION SYSTEMS	AD3112
000280	SCHNEIDER	OPERATIONS	-
000300	SMITH	OPERATIONS	-
000310	SETRIGHT	OPERATIONS	-

 

Parent topic:

Joining data from more than one table