Results 1 to 6 of 6

Thread: Query Help

  1. #1
    Join Date
    Mar 2004
    Location
    Ottawa
    Posts
    1

    Unanswered: Query Help

    Can someone view the following code and tell me why I am getting duplicate name records returning?

    SELECT DISTINCT [Input Table].ID, [Input Table].Last_Name, [Input Table].First_Name, [Input Table].Cost_Center, April.April_Premium, Aug.Aug_Premium, Dec.Dec_Premium, Feb.Feb_Premium, Jan.Jan_Premium, July.July_Premium, June.June_Premium, May.May_Premium, Nov.Nov_Premium, Oct.Oct_Premium, Sep.Sep_Premium
    FROM (((((((((((April INNER JOIN Aug ON April.ID = Aug.ID) INNER JOIN Dec ON April.ID = Dec.ID) INNER JOIN Feb ON April.ID = Feb.ID) INNER JOIN [Input Table] ON April.ID = [Input Table].ID) INNER JOIN Jan ON April.ID = Jan.ID) INNER JOIN July ON April.ID = July.ID) INNER JOIN June ON April.ID = June.ID) INNER JOIN March ON April.ID = March.ID) INNER JOIN May ON April.ID = May.ID) INNER JOIN Nov ON April.ID = Nov.ID) INNER JOIN Oct ON April.ID = Oct.ID) INNER JOIN Sep ON April.ID = Sep.ID;


    Thanks in advance for any help.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query Help

    No, one would need to know the keys of the tables. But what a design: a table per month! Why not just have one Premium table with a month column?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Did you maybe want to put a leash on the thing by insisting that something in the WHERE clause be equal to the [INPUT TABLE].ID value ??? Methinks you are getting a bounded cartesian join.

    -PatP

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Pat Phelan
    Did you maybe want to put a leash on the thing by insisting that something in the WHERE clause be equal to the [INPUT TABLE].ID value ??? Methinks you are getting a bounded cartesian join.

    -PatP
    That is there, between the join to Feb and the join to Jan - just to add to our confusion!

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by andrewst
    That is there, between the join to Feb and the join to Jan - just to add to our confusion!
    FWIW, here's a somewhat tidied up version of the query:
    PHP Code:
    SELECT DISTINCT
           
    [Input Table].ID,
           [
    Input Table].Last_Name,
           [
    Input Table].First_Name,
           [
    Input Table].Cost_Center,
           
    April.April_Premium
           
    Aug.Aug_Premium,
           
    Dec.Dec_Premium,
           
    Feb.Feb_Premium,
           
    Jan.Jan_Premium,
           
    July.July_Premium,
           
    June.June_Premium,
           
    May.May_Premium,
           
    Nov.Nov_Premium,
           
    Oct.Oct_Premium,
           
    Sep.Sep_Premium
    FROM 
    ( ( ( ( ( ( ( ( ( ( (April INNER JOIN Aug ON April.ID Aug.ID
                        
    INNER JOIN Dec ON April.ID Dec.ID
                      
    INNER JOIN Feb ON April.ID Feb.ID
                    
    INNER JOIN [Input TableON April.ID = [Input Table].ID
                  
    INNER JOIN Jan ON April.ID Jan.ID
                
    INNER JOIN July ON April.ID July.ID
              
    INNER JOIN June ON April.ID June.ID
            
    INNER JOIN March ON April.ID March.ID
          
    INNER JOIN May ON April.ID May.ID
        
    INNER JOIN Nov ON April.ID Nov.ID
      
    INNER JOIN Oct ON April.ID Oct.ID
    INNER JOIN Sep ON April.ID Sep.ID
    Quite why we start with April, then August, then December... I don't know!

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by andrewst
    That is there, between the join to Feb and the join to Jan - just to add to our confusion!
    I just hate it when I miss something like that. Ugly code (looks like MS-Access generated), but I should still have read it more carefully.

    Is there any possibility of GIGO? Could there be two (or more) rows with the same id in any of the tables (maybe from different years)?

    -PatP

Posting Permissions

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