Re: no self-joins in views?

Christoph Pingel wrote:
>> 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

You've got two columns that the system wants to call "obj_id" here. Try
something like
SELECT s.obj_id AS s_obj_id, ...

--
Richard Huxton
Archonet Ltd