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.
Bank_Accout table data:
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
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.
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?
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
select e.emp_Id,b.accout_No,ex.id from employee e,bank_accout b,expenses ex
where e.emp_id = 1 and
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,
Redesign your Expenses table to include account information.
Then do an outer join to show accounts without expense data.
Click Here to Expand Forum to Full Width