Take the 2-minute tour ×

I have two tables: Firms and Employees

I need a query so that all the Firm records are pulled, and within that query, pull the Employees for each firm.

For example:

ABC Company
Smith, John
Johnson, Tim

DEF Inc.
Jones, Mack
Frank, Tom

I have tried variations of JOIN, UNION, etc. with no luck....

share|improve this question
Is this homework? –  dmcnelis Mar 15 '11 at 21:02
add comment

2 Answers

SELECT f.FirmName, GROUP_CONCAT(e.EmpName) AS Employees 
FROM Firm f LEFT JOIN Employee e on f.FirmID = e.FirmID
share|improve this answer
This is not correct. You are missing GROUP BY f.FirmID. –  Ike Walker Mar 15 '11 at 21:27
Good catch, Ike - I've updated my answer accordingly. –  Brian Driscoll Mar 15 '11 at 23:40
add comment

A query like the below will give you a listing of all firms, and the LEFT JOIN includes companies even if it does not have any employees.

select f.name firmname, e.name
from firms f
left join employees e on e.firm_id = f.firm_id
order by firmname, e.name

From your front end display code, step through the records and whenever the firm name changes, start a new firm section.

share|improve this answer
add comment

Your Answer


