tags:

views:

60

answers:

3

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.

+1  A: 
SELECT d.name, p.name
FROM department d
JOIN people p ON p.department_id = d.id

I suggest also reading a SQL Join tutorial or three. This is a very common and very basic SQL concept that you should understand thoroughly.

hobodave
That was a softball wasn't it?
JohnFx
A: 

You can get it all in a single query with a simple join, e.g.:

SELECT   d.name AS 'department', p.name AS 'name'
FROM     department d
  LEFT JOIN people p ON p.department_id = d.id
ORDER BY department

This returns all the data, but it's a bit of a pain to consume, since you'll have to iterate through every person anyway. You can go further and group them together:

SELECT   d.name AS 'department',
         GROUP_CONCAT(p.name SEPARATOR ', ') AS 'name'
FROM     department d
  LEFT JOIN people p ON p.department_id = d.id
GROUP BY department

You'll get something like this as the output:

department | name
-----------|----------------
sales      | Tom, Bill, Rachel
marketing  | Jessica, John
Max Shawabkeh
This is exactly the type of thing I needed. Thanks for the information about GROUP BY and GROUP_CONCAT in this case. Exactly what I was looking for.
Brian
A: 

This is normally done in a single query:

SELECT DepartmentTable.Name, People.Name from DepartmentTable 
INNER JOIN People
ON DepartmentTable.id = People.department_id
ORDER BY DepartmentTable.Name

This will suppress empty departments. If you want to show empty departments, change INNER to LEFT OUTER

egrunin
-1: This query selects only departments.
hobodave
Um, what? This will return all columns from both tables, no? But as long as I'm here I'll add an ORDER BY and make the field names explicit...
egrunin