14

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.

hobodave
  • 28,925
  • 4
  • 72
  • 77
Brian
  • 953
  • 4
  • 15
  • 35
  • 1
    +1 for trying to break free of the looping mindset of the imperative programmer. It's well worth it. – spender Mar 15 '10 at 23:07

3 Answers3

16

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
  • 37,799
  • 10
  • 82
  • 91
  • 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 Mar 15 '10 at 23:07
0
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
  • 28,925
  • 4
  • 72
  • 77
0

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
  • 24,650
  • 8
  • 50
  • 93
  • 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 Mar 15 '10 at 23:17