Re: Need smart sql

On Tue, Jan 06, 2004 at 16:46:44 +0100,
Maximilian Tyrtania <Mty(at)Fischerappelt(dot)de> wrote:
> Folks,
>
> I'm in need of a little pointer on how to do the following:
>
> I have a company-table and an employee-table. The employee-table has a
> company_id field and a birthday-field. Each company-record may relate to no
> or some employee records. Nothing too exotic so far, I guess.
>
> Now I want to select all the company-records names and along with the oldest
> employee's name. Or, if there is no employee, I want to display something
> like 'no employees'.

One approach would be:

select companyname, coallesce(
(select employeename from employees where employees.company_id =
company.company_id order by birthday limit 1), 'no employees')
from company