I need help! I have three tables in a database that I want to report on. They do not have any common foreign keys,...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.