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)

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