Minimizing SQL queries using join with one-to-many relationship
- by Brian
So let me preface this by saying that I'm not an SQL wizard by any means. What I want to do is simple as a concept, but has presented me with a small challenge when trying to minimize the amount of database queries I'm performing.
Let's say I have a table of departments. Within each department is a list of employees.
What is the most efficient way of listing all the departments and which employees are in each department.
So for example if I have a department table with:
id name
1 sales
2 marketing
And a people table with:
id department_id name
1 1 Tom
2 1 Bill
3 2 Jessica
4 1 Rachel
5 2 John
What is the best way list all departments and all employees for each department like so:
Sales
Tom
Bill
Rachel
Marketing
Jessica
John
Pretend both tables are actually massive. (I want to avoid getting a list of departments, and then looping through the result and doing an individual query for each department). Think similarly of selecting the statuses/comments in a Facebook-like system, when statuses and comments are stored in separate tables.
