Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    31

    Unanswered: "outer join" --> not getting all wanted results

    Hi all,

    I'm having 2 tables :

    Table 1 (consists all features):

    feat
    category


    Table 2

    country
    feat (code to be linked with table1)
    featname
    count

    What i want : all feats from table1 and, if existing, the count for the feat from table2 (so if Table1 has 9 records and Table2 has 8, I want 9 records, of which 8 with count, 1 without count)


    Problem is, I only get 8 records.


    The sql (a bit more fields than in above example but ...)

    SELECT FeatClass.Country, qryPOICategories.category, FeatClass.Featclass, FeatClass.Featname, Sum(FeatClass.Count) AS sumCount, Sum(FeatClass.PreviousCount) AS sumPreviousCount, Sum([count]-[previouscount]) AS Difference
    FROM qryPOICategories LEFT JOIN FeatClass ON qryPOICategories.feat = FeatClass.Featclass
    GROUP BY FeatClass.Country, qryPOICategories.category, FeatClass.Featclass, FeatClass.Featname;


    With qryPOICategories serving as Table1, featclass serving as Table2.

    Problem is with some countries, I get 4 feats in a category, other countries I get 7 feats.

    Anybody got an idea as to what the problem is ???


    Grts,
    kd

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Setup the relatio0nship to give you all records from the first table and only matching records from the second table.

    Then, group by feature and count feature.

    This can all be done in the QBE grid, so I won't "air code" the SQL.

    tc

Posting Permissions

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