Ask the Expert

Combining unrelated queries

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:

  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 
ON rm.dp_id = dp.dp_id 
ON dp.dp_id = gp.dp_id

    Requires Free Membership to View

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
    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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: