Query problem using groups

Hi
Is there any way to run a query on a pair of related tables with a one
to many relationship such that for each parent record you include the
details of just one child
e.g. Employees contains a foreign key to the identity of Departments
I want to select the department name and the first employee in the
database associated with that department
The query below would select all employees for all departments
SELECT deptname, surname, firstname from Departments inner join
Employees on Department.DeptId = Employee.DebtId
I need to select just the first employee.
Any ideas?
Thanks
Damien
How do you define which is the "first" emloyee? (Longest serving maybe? Or
most senior?) Tables have no inherent order so you have to define "first"
based on the information in your data. You could use the date they started:
SELECT deptname, surname, firstname
FROM Departments AS D
INNER JOIN Employees AS E
ON D.deptid = E.deptid
AND E.date_started =
(SELECT MIN(date_started)
FROM Employees
WHERE deptid = D.deptid)
but that may still give multiple rows if more than one person started on the
same day. Maybe you just want ANY one employee but don't really care which.
In that case just use the primary key of the Employees table in place of the
date in this query (assuning you have a single-column key).
Hope this helps.
David Portas
SQL Server MVP
|||Yes that solves it for me, thank you.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!