Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005
    Posts
    3

    Unanswered: SQL Question plz help!

    Hi whats up?
    pretty new to delphi+sql, so here's my question.

    i've got 2 tables (simplified)

    company
    --------
    id (primary key)
    name


    employee
    ---------
    id (primary key)
    company_id (foreign key)
    name

    the result i want is to get all the company names listed with their employee's. the result must also contain company's where no employee has been filled in.

    i used this query:

    select company.name, employee.name
    from company, employee
    where employee.company_id = company.id
    order by employee.name asc

    this statement works out fine, except the company's with no employee's are left out...

    can somebody please help me with this one?

    tnx in advance!

    ps (sorry for my bad english, i'm dutch)

  2. #2
    Join Date
    Nov 2005
    Posts
    1
    Hi

    an outer join would help you i think. If you try this (at least for Oracle SQL)

    select company.name, employee.name
    from company, employee
    where employee.company_id (+) = company.id
    order by employee.name asc

    Note: The companies that has no employees will be placed last if im not misstaken. The order by places NULL values last.

    You could use also the NVL command to present the null values as a single blank for example, and then it would sort different.

    Maybe something like this:

    select company.name as Company_Name, nvl(employee.name, ' ') as Employee_Name
    from company, employee
    where employee.company_id (+) = company.id
    order by Employee_Name asc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •