Could you give an actual example?
Sure. The idea is that 'objects' (persons, books, places) from a table obj are linked with each other in a link table ool where objects from obj can appear in an 'subject' or a 'object' column. Since the relation can be any, this is a very flexible and lean design for an 'ontology-like' collection of facts.
As I said, the CREATE VIEW returns an error (column obj_id duplicated), while the select statement by itself works (with an additional constraint on s.obj_id, otherwise the db would throw up hundreds of thousands of rows).
CREATE VIEW relations_aspect_subject AS
SELECT s.obj_id, s.canonical_name, rlt.dscr, rlt.rlt_id, o.obj_id, o.canonical_name
FROM obj s, obj o, ool, rlt
WHERE s.obj_id = ool.subject
AND o.obj_id = ool.object
AND rlt.rlt_id = ool.relation
ORDER BY rlt_id
Do I miss something obvious?
thanks, Christoph
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
