Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Problems Querying multi table database

    I have built a four table database with one parent table connected to three other tables through three one to many relationships. as diagrammed below. I am trying to query all four tables so as to create a report about each entry in the parent table. My problem is that when i run the query i get a Cartesian product and the entrees are multiplied four times. Any help would be awesome.

    Tables

    Parent table
    ID
    Agency - Account - primary key

    Estimates table
    Agency Account - Foreign Key
    Amount
    footnote#

    Approps table
    Agency Account - Foreign Key
    Amount
    footnote#

    Footnote Table table
    Agency Account - Foreign Key
    Footnote #
    footnote

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You must use INNER JOINs to link the tables:
    Code:
    SELECT Parent.ID, 
           Parent.Agency_Account, 
           Approps.Amount, 
           Approps.Footnote, 
           Estimates.Amount, 
           Estimates.Footnote, 
           FootNote.Amount, 
           FootNote.Footnote
    FROM ((Parent INNER JOIN Approps ON Parent.Agency_Account = Approps.Agency_Account) 
                  INNER JOIN Estimates ON Parent.Agency_Account = Estimates.Agency_Account) 
           INNER JOIN FootNote ON Parent.Agency_Account = FootNote.Agency_Account;
    Anyway, there probably should be a single table for Approps, Estimates and FootNote as the 3 tables have the same structure. One additional column would indicate the type of row (Approps, Estimates or FootNote).
    Have a nice day!

Posting Permissions

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