Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    17

    Unanswered: How do i Joine these three tables??

    I have three tables, i'm looking for a specific type of join which i do not have the skill to code yet. Basically i want to know which programs have been changed(combining Program_ID's of two change tables) and comparing them with the master table. Because of the complexity of the DB relationships, the two change tables have nothing in common but the Program_ID numbers

    Here's the join that i require:


    ( Inner Join ( Unknown Join )) =Program_ID of
    1st table 2nd table changed programs
    Program_ID Program_ID Program_ID Output
    1 1 1 1
    2 3 2 2
    3 5 3 3
    4 6 5
    5 6
    6


    The first is the main table which holds information about every credit card program in the database. This is the breakdown of it

    Table(Program_Info)
    *Program_ID-Autonumber
    Program_Name-text
    etc...

    The second table holds information about changes to the credit card programs.

    Table(Program_Changes)
    *Change_ID-Autonumber
    *Program_ID-Integer linking to Program_Info.Program_ID
    etc...

    The third table holds information about changes to the credit cards associated products. There is a one-many relationship between this table and Program_Info.

    Table(Product_Changes)
    *Change_ID-Autonumber
    *Program_ID-integer linking to Program_Info.Program_ID
    *Product_ID-Another part of the key

  2. #2
    Join Date
    Sep 2003
    Posts
    17

    Re: How do i Joine these three tables??

    It doesn't look like my chart came out too well like that. Here's a clarification

    Table 1) 1,2,3,4,5,6,7,8
    Table 2) 2,3,5,6
    Table 3) 1,2,5,6

    Output) 1,2,3,5,6
    The output is taken by combining the Program_ID's of the 2nd, 3rd table and inner joining that with the Program_Info table.

    I need this for an output report that i'm doing which lists all the changes to the database. Right now i have 90 programs with say 14 changed and a lot of white space on the pages.

  3. #3
    Join Date
    Oct 2002
    Location
    Argentina
    Posts
    72
    you must make a UNION of tables and then join with table 1

    Something like this:


    select table2.* from table2,table1 WHERE table1.id=table2.id
    UNION
    select table3.* from table3,table1 WHERE table1.id=table3.id



    hope this helps

  4. #4
    Join Date
    Sep 2003
    Posts
    17
    Thanks for your help, i figured out how to do what i want but now i have a small problem with my report:

    Here was my solution:

    Query Change1:
    Select Change_ID, Program_ID, Year, Quarter, Description FROM Program_Changes
    UNION Select Change_ID, Program_ID, Year, Quarter, Description FROM Product_Changes;


    The Reports Control Source:
    SELECT Change1.Change_ID, Program_Info.Program_ID, Program_Info.Program_Name, Change1.Year, Change1.Quarter, Change1.Description
    FROM Change1 INNER JOIN Program_Info ON Change1.Program_ID = Program_Info.Program_ID
    ORDER BY Program_Info.Program_Name;


    It's doing everything i want it too now, but there is an annoying bug.

    For example, Program A.B.C. and D have had one change done to them during this quarter. They all print perfectly on the output report.

    Program X,Y, and Z have had 2-5 changes done to them and they repeat themselves on my report that many times. The CITIBANK AAdvantage program has been changed 6 times and the netire group of changes repeats 6 times on the output report. With the SQL i've input above, can anyone see why this error would be occurring?
    Last edited by AdvantageC; 12-05-03 at 11:10.

  5. #5
    Join Date
    Oct 2002
    Location
    Argentina
    Posts
    72
    try without the description field

    Originally posted by AdvantageC
    Thanks for your help, i figured out how to do what i want but now i have a small problem with my report:

    Here was my solution:

    Query Change1:
    Select Change_ID, Program_ID, Year, Quarter, Description FROM Program_Changes
    UNION Select Change_ID, Program_ID, Year, Quarter, Description FROM Product_Changes;


    The Reports Control Source:
    SELECT Change1.Change_ID, Program_Info.Program_ID, Program_Info.Program_Name, Change1.Year, Change1.Quarter, Change1.Description
    FROM Change1 INNER JOIN Program_Info ON Change1.Program_ID = Program_Info.Program_ID
    ORDER BY Program_Info.Program_Name;


    It's doing everything i want it too now, but there is an annoying bug.

    For example, Program A.B.C. and D have had one change done to them during this quarter. They all print perfectly on the output report.

    Program X,Y, and Z have had 2-5 changes done to them and they repeat themselves on my report that many times. The CITIBANK AAdvantage program has been changed 6 times and the netire group of changes repeats 6 times on the output report. With the SQL i've input above, can anyone see why this error would be occurring?

  6. #6
    Join Date
    Sep 2003
    Posts
    17
    Originally posted by fhunth
    try without the description field
    The description describes the changes made so it's important to keep that information. I'm not sure what is going on because when i run the reports rowsource query, the information that comes up is perfect. When i view the report, it's repeating some groups of data and i don't see why.

Posting Permissions

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