Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    table joining two instances of the same class

    Hi all!

    I have a problem setting up a database in OpenOffice.org Base, and I suspect it is a design problem. I want to map recommendations on human rights policy that countries can give each other at the UN. To map this, I have three tables:

    Issues: PK IssueNo | Issue (e.g. 'child labour', 'juvenile justice' etc.)

    Countries: PK CountryNo | CountryName | Region (e.g. Africa)

    Recommendations: RecommNo | CountryAffected | CountryProposing | Issue

    CountryAffected and CountryProposing should both contain CountryNos from 'Countries'. So I have two relationships between two tables. In OpenOffice's relationships window, I can design such a relationship, but if I try to wrap everything into a query showing the countries' names, it doesn't know which countryname it has to display for a column, as there are two relationships...
    Could anyone help me out how to fix this? I am quite new to DB design...

    Thanks!
    Philippe

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you post your SQL please?

  3. #3
    Join Date
    Apr 2010
    Posts
    2
    The SQL for the query is here:
    Code:
    SELECT "atmUprSessions"."SessionNo", "relRecommendations"."CountryAffected", "atmCountries"."CountryName", "relRecommendations"."Issue" FROM "relRecommendations", "atmCountries", "atmIssues", "atmUprSessions" WHERE "relRecommendations"."CountryAsking" = "atmCountries"."CountryNo" AND "relRecommendations"."Issue" = "atmIssues"."IssueNo" AND "relRecommendations"."UprSession" = "atmUprSessions"."UprNo"

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't know the syntax for that RDBMS but hopefully you can see what is going on:
    Code:
    SELECT "atmUprSessions"."SessionNo"
        , "relRecommendations"."CountryAffected"
        , "CountryProposing"."CountryName" AS "CountryProposing"
        , "CountryAsking"."CountryName" AS "CountryAsking"
        , "relRecommendations"."Issue" 
    FROM     "relRecommendations", "atmCountries" AS "CountryAsking", "atmIssues", "atmUprSessions", "atmCountries" AS "CountryProposing"
    WHERE     "relRecommendations"."CountryAsking" = "CountryAsking"."CountryNo" 
        AND "relRecommendations"."Issue" = "atmIssues"."IssueNo" 
        AND "relRecommendations"."UprSession" = "atmUprSessions"."UprNo"
        AND "relRecommendations"."CountryProposing" = "CountryProposing"."CountryNo"
    If not, look up "aliases"

Posting Permissions

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