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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in February 2004