I need help! I have three tables in a database that I want to report on. They do not have any common foreign keys, but they do share some common fields between them. One table is a Departments table, the other a Rooms table and the third an Employee table.

I want to group data by department showing the employees and rooms for each department. However some departments don't have rooms and some don't have employees. I have tried several ways to build a SELECT statement that would show me all the employees and rooms by department and show null where there are none. I keep getting dupilicate entries of employees or rooms and no null per department.

What is the proper way to build a select statement that would show the above? Here is my last effort:

SELECT DISTINCT 
  rm.bl_id, rm.fl_id, 
  rm.dv_id, rm.dp_id, 
  gp.gp_id, gp.area, 
  rm.rm_id, rm.area AS Expr1
FROM rm 
LEFT OUTER JOIN dp 
ON rm.dp_id = dp.dp_id 
RIGHT OUTER JOIN gp 
ON dp.dp_id = gp.dp_id

    Requires Free Membership to View

    Login

The Departments table has two unrelated and separate one-to-many relationships to Rooms and Employees. If, say, the Payroll department has three rooms and five employees, then your query would return 15 rows. There's no way a join makes sense. What we need here is two unrelated and separate queries, one for each relationship. We combine them "vertically" with the UNION operator:

select dp.dp_name    as dept
     , 'empl'        as type
     , gp.gp_name    as name
  from depts dp
left outer 
  join empls gp 
    on dp.dp_id = gp.dp_id
union all  
select dp.dp_name
     , 'room' 
     ,  rm.rm_name
  from depts dp 
left outer
  join rooms rm
    on dp.dp_id = rm.dp_id
order
    by 1, 2, 3

A row for each department without rooms, and a row for each department without employees, will be included, with NULL in the name. Results are sorted by department, and within department, by employees first, then rooms.

For More Information


This was first published in February 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top