The original question was database specific, but perhaps this is a good place to include a more generic answer. It's a common question. The concept that you are describing is often referred to as 'Group Concatenation'. There's no standard solution in SQL-92 or SQL-99. So you'll need a vendor-specific solution.
- MySQL - Use the built-in GROUP_CONCAT function. In your example you would want something like this:
select
o.ID, o.Address, o.OtherDetails,
GROUP_CONCAT( concat(e.firstname, ' ', e.lastname) ) as Employees
from
employees e
inner join organization o on o.org_id=e.org_id
group by o.org_id
- PostgreSQL - PostgreSQL 9.0 is equally simple now that string_agg(expression, delimiter) is built-in. Here it is with 'comma-space' between elements:
select
o.ID, o.Address, o.OtherDetails,
STRING_AGG( (e.firstname || ' ' || e.lastname), ', ' ) as Employees
from
employees e
inner join organization o on o.org_id=e.org_id
group by o.org_id
PostgreSQL before 9.0 allows you to define your own aggregate functions with CREATE AGGREGATE. Slightly more work than MySQL, but much more flexible. See this other post for more details. (Of course PostgreSQL 9.0 and later have this option as well.)
Oracle - same idea using LISTAGG.
MS SQL Server - same idea using STRING_AGG
Fallback solution - in other database technologies or in very very old versions of the technologies listed above you don't have these group concatenation functions. In that case create a stored procedure that takes the org_id as its input and outputs the concatenated employee names. Then use this stored procedure in your query. Some of the other responses here include some details about how to write stored procedures like these.
select
o.ID, o.Address, o.OtherDetails,
MY_CUSTOM_GROUP_CONCAT_PROCEDURE( o.ID ) as Employees
from
organization o
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…