Thread: Need smart sql

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'.

Anyone?

-> Maximilian Tyrtania | Mty@fischerAppelt.de
  fischerAppelt Kommunikation GmbH
  Tucholskystr. 18 | D-10117 Berlin | Germany
  Tel. ++49-30-726146-728 | Fax ++49-30-726146-710
  www.fischerAppelt.de
--

On Tue, Jan 06, 2004 at 16:46:44 +0100,
  Maximilian Tyrtania <Mty@Fischerappelt.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