table joining two instances of the same class

pagin · #1 (**permalink**) 04-21-10, 04:41

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

pootle flump · #2 (**permalink**) 04-21-10, 04:54

Can you post your SQL please?

pagin · #3 (**permalink**) 04-21-10, 05:15

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"

pootle flump · #4 (**permalink**) 04-21-10, 05:28

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"

Internet Services

Web Development

Digital Marketing

Consumer Tech