0

Hi I'm the following situation.

I have 5 tables

Entity
------------  
idEntity
entityName
...
...
idCountry_fkey
idRegion_fkey
idAggregate_fkey

Country
---------------
idCountry
countryName

Region
---------------
idCountry_fkey
idRegion
RegionName

Aggregate
---------------
idAggregate
AggregateName

Aggregate_to_Country
----------------------
idAC
idCountry_fkey
idAggregate_fkey

I enforced mutual exclusivity with the following constraint con the Entity Table

    CHECK (
    (("idAggregate" IS NULL) AND ("idRegion" IS NULL) AND ("idCountry" IS NOT NULL)) 
    OR 
    (("idAggregate" IS NULL) AND ("idRegion" IS NOT NULL) AND ("idCountry" IS NULL)) 
    OR 
    (("idAggregate" IS NOT NULL) AND ("idRegion" IS NULL) AND ("idCountry" IS NULL))
);

I've the following questions:

  1. is this the proper way to handle this situation
  2. How do I query in order to have:
index | entityname | Aggregate | Country | Region | Value
----------------------------------------------------------
case1 |  A         |  Eurozone |  null   | null   | 1
----------------------------------------------------------
case2  |  B        |    LATAM  |  Brazil | null   | 22
----------------------------------------------------------
case3  |  C        |  Europe   |  Italy  | Lazio | 44
  1. Case 1) when Entity is related to Aggregate
  2. Case 2) When Entity is related to Country, and the Country is in an Aggregate Case
  3. Case 3) When Entity is related to Region, and the Region is Related To a Country, and the Country is in an Aggregate
  • In theory every country should have regions but in practice this may not be so. Vatican City comes to mind... Some more thought on the inevitable exceptions to the rule might help you – kevinsky Jun 5 at 12:05
1

It will be something like:

SELECT e.index,
       e.entityname,
       COALESCE(a1.AggregateName, a2.AggregateName, a3.AggregateName) AggregateName,
       COALESCE(c2.CountryName, c3.CountryName) CountryName, 
       r3.Region,
       e.Value
FROM Entity e
-- when idAggregate_fkey is specified
LEFT JOIN Aggregate a1 ON a1.idAggregate = e.idAggregate_fkey
-- when idCountry_fkey is specified
LEFT JOIN Country c2 ON c2.idCountry = e.idCountry_fkey
LEFT JOIN Aggregate_to_Country ac2 ON ac2.idCountry_fkey = e.idCountry_fkey
LEFT JOIN Aggregate a2 ON a2.idAggregate = ac2.idAggregate_fkey
-- when idRegion_fkey is specified
LEFT JOIN Region r3 ON r3.idRegion = e.idRegion_fkey
LEFT JOIN Country c3 ON c3.idCountry = r3.idCountry_fkey
LEFT JOIN Aggregate_to_Country ac3 ON ac3.idCountry_fkey = c3.idCountry_fkey
LEFT JOIN Aggregate a3 ON a3.idAggregate = ac3.idAggregate_fkey
  1. Check join order and conditions for each branch - I may make a mistake.
  2. Check if some joins may be INNER.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.