Q

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:

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

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close