Selecting data from more than 2 tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Selecting data from more than 2 tables

  1. #1
    Join Date
    Apr 2000
    Posts
    9
    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.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2001
    Posts
    515

    Try This

    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;

  4. #4
    Join Date
    Apr 2000
    Posts
    9
    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.

  5. #5
    Join Date
    Jan 2000
    Location
    Silver Spring MD USA
    Posts
    105
    Redesign your Expenses table to include account information.
    Then do an outer join to show accounts without expense data.

Posting Permissions

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


Click Here to Expand Forum to Full Width