How to get multiple records against one record based on relation?

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

Since the question is tagged as MySQL, you should be able to use a MySQL-specific solution, namely, GROUP_CONCAT. For example,

select Org_ID, Org_Address, Org_OtherDetails,
       GROUP_CONCAT(employees) as Employees
from  employees a, organization b
where a.org_id=b.org_id
group by b.org_id;

in MS SQL you can do:

create function dbo.table2list (@input int)
returns varchar(8000)
as
BEGIN
declare @putout varchar(8000)
set @putout = ''
select @putout = @putout + ', ' + <employeename>
from <employeetable>
where <orgid> = @input
return @putout
end

then do:

select * from org, dbo.table2list(orgid)
from <organisationtable>

I think you can do it with COALESCE() as well, but can't remember the syntax off the top of my head

How to get multiple records against one record based on relation?

Related

Recent Posts