Selecting data from more than 2 tables

[kadari]

Hi,
I'm having some problem with selecting data from 3 tables.Say I've 3 tables Employee,Bank_account and Expenses.Bank_account and expenses are referncing Employee table through Emp_Id field.I want to see the Account information and expense information of one employee (for example emp_id=1).But when I select data from thsese three tables using equal join for 2 tables,I'm getting duplicated values.

Employee Data:

Emp_Id Name
1 XXXX
2 YYYY

Bank_Accout table data:

Accout_No Emp_Id
100 1
200 1
300 2


Expenses information:

Id Emp_Id
20 1
30 2

Select Employee.emp_Id,accout_No,id from employee,bank_accout,expenses
where Employee.emp_id=Bank_account.emp_id and employee.emp_id=expenses.emp_id
and employee.emp_id=1;

This is showing the results like this:

Emp_id Accout_no id
1 100 20
1 200 20

But I want my result to look like this:
Emp_id Account_no id
1 100 20
1 200 -

I'm trying to make a report with this data.If some one knows about crystal reports please help me to get this result.
Thanks,
Kadari.

[jmodic]

You don't have an error in setting up your query, you have a design error! Your EXPENSES table lacks the account number information. You don't have information on which employee's account the expenses should be assigned to. You only have information on expenses per employee, not per employee's account. So you how can you say all the expenses from emp# 1 should go to his account# 100 and none to account# 200?

[lesstjm]

select e.emp_Id,b.accout_No,ex.id from employee e,bank_accout b,expenses ex
where e.emp_id = 1 and
b.emp_id=e.emp_id and
ex.emp_id(+)=b.emp_id;

[kadari]

Thanks for reply.I tried the query suggested but it is not working.May be the design of tables has some error.But if anyone has any other ideas please let me know.

Thanks in advance,
kadari.

[maachan]

Redesign your Expenses table to include account information.
Then do an outer join to show accounts without expense data.