Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2008
    Posts
    35

    Unanswered: Query works correctly until one table is added

    Just like the title, I've got a query written that works perfectly until I add one table, and after that my count skyrockets. I mean, I'm talking 10000 instead of 4. I'm not even calling any fields out of this new table, it happens just by calling out the table. It's simple, it looks for a column and counts how many are there. I just want to add a date, which is kept in another table. Any ideas? I've never run across this kind of behavior before.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    punkn00dlez, what is probably happening is you do not have a Join predicate (TAB1.COL = TAB2.COL).

    What happens is the first row in table 1 is joined with EVERY row of table 2, then the second row of table 1 is joined with EVERY row of table 2, etc. until you have gone through the entire table 1 rows.

    The number of rows returned is Table1 rows * Table2 rows (for example, 1,000 * 300 = 300,000 rows returned).

  3. #3
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Check the relationships between the offending tables and the other tables in the query. If nothing jumps out at you, post back the table structures and query sql.

  4. #4
    Join Date
    Oct 2008
    Posts
    35
    It runs correctly with this:
    Code:
    SELECT tblReason.Reason, Count(tblBOMTracking.Reason) AS CountOfReason
    FROM tblReason INNER JOIN tblBOMTracking ON tblReason.Reason = tblBOMTracking.Reason
    GROUP BY tblReason.Reason;
    Incorrectly with this:
    Code:
    SELECT tblReason.Reason, Count(tblBOMTracking.Reason) AS CountOfReason
    FROM tblECN, tblReason INNER JOIN tblBOMTracking ON tblReason.Reason = tblBOMTracking.Reason
    GROUP BY tblReason.Reason;

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    In your working example:
    Code:
    SELECT tblReason.Reason
         , Count(tblBOMTracking.Reason) AS CountOfReason
    FROM tblReason 
           INNER JOIN 
         tblBOMTracking 
           ON tblReason.Reason = tblBOMTracking.Reason
    GROUP BY tblReason.Reason;
    You are joining TblReason and tblBOMTracking with ON tblReason.Reason = tblBOMTracking.Reason.

    However, on your non-working example:
    Code:
    SELECT tblReason.Reason
         , Count(tblBOMTracking.Reason) AS CountOfReason
    FROM tblECN
       , tblReason 
           INNER JOIN 
         tblBOMTracking 
           ON tblReason.Reason = tblBOMTracking.Reason
    GROUP BY tblReason.Reason;
    You still have the join predicate on the two original tables but you added tblECN and this does NOT have an join predicate. You need something like:
    Code:
    SELECT tblReason.Reason
         , Count(tblBOMTracking.Reason) AS CountOfReason
    FROM tblECN
           INNER JOIN 
         tblReason 
           ON tblECn.col-name = tblReason.column.name
           INNER JOIN 
         tblBOMTracking 
           ON tblReason.Reason = tblBOMTracking.Reason
    GROUP BY tblReason.Reason;

  6. #6
    Join Date
    Oct 2008
    Posts
    35
    When I join the proper fields, I get an ambiguous outer joins error.
    Code:
    SELECT tblReason.Reason, Count(tblBOMTracking.Reason) AS CountOfReason
    FROM tblECN LEFT JOIN (tblReason INNER JOIN tblBOMTracking ON tblReason.Reason = tblBOMTracking.Reason) ON tblECN.ECN = tblBOMTracking.ID
    GROUP BY tblReason.Reason;
    What does that mean?

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    You are going to have to rethink what it is you want to accomplish. This query doesn't make sense:
    Code:
    SELECT tblReason.Reason
    , Count(tblBOMTracking.Reason) AS CountOfReason
    FROM tblECN 
           LEFT JOIN 
           (tblReason 
              INNER JOIN 
              tblBOMTracking 
                ON tblReason.Reason = tblBOMTracking.Reason
            ) 
            ON tblECN.ECN = tblBOMTracking.ID
    GROUP BY tblReason.Reason;
    You are using tblECN as the 'anchor' for a Left outer Join (every qualified row will be returned even if there is not a matching row on the other table tblBOMTracking). But you are selecting a column form tblBOMTracking table to do you Summarize and get Counts. Unless you want counts of all the NULLs for non-matching rows, this doesn't make sense.

    As for the ambiguous error, that means, the database engine can't figure out what column to use.

    I believe the problem is that you put parenthesis around the two table being Inner Joined. This makes it a derived table. As such you need to Select columns and give the derived table an alias-name. As it is, when it tries to use the ON tblECN.ECN = tblBOMTracking.ID is processed, the table tblBOMTracking no longer exists. It was used in the derived table and now it is trying to join tblECN to the derived table and can't find the column ID in it.

    To correct the SQL (but not necessarily the logic) use:
    Code:
    SELECT TAB.Reason
    , Count(*) AS CountOfReason
    FROM tblECN 
           LEFT JOIN 
           (SELECT tblReason.ID, tblReason.Reason
            FROM  tblReason 
                    INNER JOIN 
                    tblBOMTracking 
                      ON tblReason.Reason = tblBOMTracking.Reason
            )  AS TAB
              ON tblECN.ECN = TAB.ID
    GROUP BY TAB.Reason;
    Last edited by Stealth_DBA; 06-10-09 at 09:52.

Posting Permissions

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