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

Question

I have two tables Organisation and Employee having one to many relation i.e one organisation can have multiple employees. Now I want to select all information of a particular organisation plus first name of all employees for this organisation. What’s the best way to do it? Can I get all of this in single record set or I will have to get multiple rows based on no. of employees? Here is a bit graphical demonstration of what I want:

Org_ID      Org_Address    Org_OtherDetails    Employess

1           132A B Road    List of details     Emp1, Emp2, Emp3.....

Answer 1

If you use Oracle you can create a PL/SQL function you can use in your query that accepts an organization_id as input, and returns the first name of all employees belonging to that org as a string. For example:-

select
   o.org_id,
   o.org_address,
   o.org_otherdetails,
   org_employees( o.org_id ) as org_employees
from
   organization o

Answer 2

It all depends. If you do a join, you get all the organization data on every row. (1 row per employee). That has a cost. If you do two queries. (Org and Emp) that has a different cost.

Pick your poison.

Answer 3

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

Answer 4

The short answer is "no".

As noted in other answers, there are vendor-specific ways to achieve this result, but there is no pure SQL solution which works in one query.

sorry about that :(

Presumably one of the vendor specific solutions will work for you?

Answer 5

Here's what you can do, you have 2 options:

select *
FROM
  users u
  LEFT JOIN organizations o ON (u.idorg = o.id);

This way you will get extra data on each row - full organization info you don't really need.

Or you can do:

select o.*, group_concat(u.name)
FROM
  users u
  LEFT JOIN organizations o ON (u.idorg = o.id)
GROUP BY
  o.id

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

The second approach is applicable if you want to see ie. list of usernames "user1, user2, user3", but don't want to operate on the fields themselves...

Answer 6

Sorry I didn't mention my database specifications, but I thought it would be feasible using standard SQL. Anyhow I just need it in MySQL. @michal Thanks for the link. I am not sure about the part that says "don't want to operate on the fields themselves..". Could shed some light on it. Actually I just need to outpout full name of employees which is the combination of first name, middle name and last name. So will it allow me to group contact on more than one colum?

Thanks to all for your replies!

Answer 7

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;

Answer 8

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 - Probably the simplest solution. 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 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.

• Oracle & MS SQL Server - 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

• Other DBMS technologies - The stored procedure route is the most likely. Perhaps others can update this answer with more technology specific answers.

Answer 9

Khan,

mdahlman showed how to get first name and last name. By 'could not operate on the fields' I meant that the employees will be grouped by organization and returned in a single field joint by default by comma. If you want to parse them separately, you will need to explode the value.

Answer 10

Thanks a lot mdahlman and michal for your detailed answers and clarification. Everything is now great and crystal clear. Cheers