多表查询一例子

四张表

T_ORGANIZATION

T_EMPLOYEE_ROLE

T_ROLE

T_EMPLOYEE

 

T_EMPLOYEE
--------------- 
ID 员工编号 
FIRSTNAME  用户名 
LASTNAME   用户名

 

T_ORGANIZATION
---------------
ID 
PARENT_ID 
NAME 
DESCR 组织全称

T_ROLE
--------------- 
ID 
NAME 角色名称 
DESCR

T_EMPLOYEE_ROLE 
-------------
ID 
EMPLOYEE_ID 
ROLE_ID 
ORG_ID

select e.ID,(LASTNAME||FIRSTNAME)as EMPNAME,o.NAME as ORGNAME,r.DESCR as ROLENAME from T_EMPLOYEE_ROLE er left join T_EMPLOYEE as e on e.ID= er.EMPLOYEE_ID left join T_ORGANIZATION o on o.ID =er.ORG_ID left join T_ROLE r on r.ID = er.ROLE_ID ORDER BY ID DESC 分清哪个是主表是关键