0

I try to write a SQL query (using full outer join) that creates a full result set from three tables that are related to each other by n:m-relationships.

The initial situation is a normalized data model with three entities: person, artifact and location. Each of the entities are related to every other entity via a n:m-relationship which is represented by intersection tables.

person { person_id, ... }  
artifact { artifact_id, ... }
location { location_id, ... }

rel_person_artifact { person_id, artifact_id, role_id }
rel_person_location { person_id, location_id, role_id }
rel_artifact_location { artifact_id, location_id, role_id }

My goal is to create a result set from all three tables that contains all persons, artifacts and locations.

To solve the problem I tried the following statement:

select
  *
from
  person per
  full outer join rel_person_artifact rpa on per.person_id = rpa.person_id
  full outer join artifact art on art.artifact_id = rpa.artifact_id
  full outer join rel_artifact_location ral on ral.artifact_id = art.artifact_id
  full outer join location loc on loc.location_id = ral.location_id
  full outer join rel_person_location rpl on rpl.person_id = per.person_id;

This query is syntatically correct but is returns worng content because the join handles one table after the other. At the end of the day not every relationship is represented in the result set.

Is there any way to combine the above tables in order to get a complete and correct result set?

EDIT. Here are some sample records (I stick to the intersection tables):

rel_person_artifact ( 'Peter', 'car 1', 'owner' ), ( 'Alex', 'car 1', 'driver' )
rel_person_location ( 'Peter', 'location 1', 'home' ), ( 'Paul', 'location 2', 'place of work' )
rel_artifact_location ( 'car 1', 'location 1', 'parking' )

Of course the data can be much more complex than this example. As mentioned in a comment below there might also be circual relationships.

I'm not sure how a result set should look like, i'm not even sure weather this combination is possible at all?

Thank you very much!

Regards

lennard
  • 11
  • 3
  • Dont use `OUTER JOIN` instead use `INNER JOIN` rather. I m not sure but it might works. – Ankit Bajpai Nov 03 '14 at 09:50
  • 2
    It might be a good idea to give some sample data and an example result set. – Fred Nov 03 '14 at 09:52
  • Consider just the following 3 rows in your intersection tables: rel_person_artifact('Jim','Pencil','x'); rel_person_location('Jim','London','y'); rel_artifact_location('Pencil','Paris','z'); - what would the result of your query be? – Tony Andrews Nov 03 '14 at 10:04
  • In addition to providing sample data, please also explain what `role_id` is. – Gordon Linoff Nov 03 '14 at 10:50
  • Thanks for the response! The role_id defines the kind or the relationship. For example a person is related to a location with the role "home" another relation might have the role "place of work". But I think these roles are not directly connected to the join problem. – lennard Nov 03 '14 at 12:24
  • Refering to the example from Tony, this is one of the big problems. I'm not sure how to combine these records correcly because they might be in a circulary relationship like in your case. Is there a way to join these tables correcly at all? – lennard Nov 03 '14 at 12:34
  • @AnkitBajpai I tried to replace the full outer joins with inner joins. This looked very promising. The resultset is basically correct, but the query only returns records that are used in every single relationship, right? This means I would loose records without relationships. – lennard Nov 03 '14 at 13:10
  • If you want every single data, then you can use `CROSS JOIN` rather. – Ankit Bajpai Nov 04 '14 at 07:53

1 Answers1

0

I finally solved the problem by using a combination of LEFT JOIN and UNION. You can dissolve relationships that belong to a certain perspective. That means you build the needed perspectives via LEFT JOIN first and then combine these perspectives via UNION.

So, the given example can be solved as follows:

-- person perspective
select
  per.person_id person_id,
  art.artifact_id artifact_id,
  loc.location_id location_id,
  rpa.role_id person_artifact_role_id,
  rpl.role_id person_location_role_id,
  null artifact_location_role_id -- This attribute cannot be filled in this perspective, so it has to be null to fit the common structure of both perspectives.
from 
  person per
  left join rel_person_artifact rpa on rpa.person_id = per.person_id
  left join artifact art on art.artifact_id = rpa.artifact_id
  left join rel_person_location rpl on rpl.person_id = per.person_id
  left join location loc on loc.location_id = rpl.location_id

union

-- location perspective
select
  per.person_id person_id,
  art.artifact_id artifact_id,
  loc.location_id location_id,
  null person_artifact_role_id, -- see above
  rpl.role_id person_location_role_id,
  ral.role_id artifact_location_role_id
from
  location loc
  left join rel_artifact_location ral on ral.location_id = loc.location_id
  left join artifact art on art.artifact_id = ral.artifact_id
  left join rel_person_location rpl on rpl.location_id = loc.location_id
  left join person per on per.person_id = rpl.person_id

-- ...

Every perspective that is connected with a UNION as to fit a common structure, so you will have to adjust every perspective when adding a new one.

Thanks for your response, hope this will help anybody facing a similar problem.

lennard
  • 11
  • 3