CODEWITHSUNDEEP
sqloracle-databasevieworacle-apex
botenvouwer
botenvouwer

Reputation: 4432

How to create view with multiple joins on same table

In my database I have these two tables:

Person

+----+---------+---------+
| pk |  name   | sirname |
+----+---------+---------+
|  1 | john    | leno    |
|  2 | william | wallice |
|  3 | eva     | apple   |
|  4 | walter  | white   |
+----+---------+---------+

Request

+----+-------------+----------+---------------+---------+---------+
| pk | requestdate | accepted | requestperson | parent1 | parent2 |
+----+-------------+----------+---------------+---------+---------+
|  1 | 1/1/2014    | Y        |             1 | 2       | 3       |
|  2 | 1/2/2014    | N        |             4 | NULL    | NULL    |
+----+-------------+----------+---------------+---------+---------+

To get the requests I do:

SELECT * 
FROM request
LEFT JOIN person p_subject ON requestperson = p_subject.pk
LEFT JOIN person p_parent1 ON parent1 = p_parent1.pk
LEFT JOIN person p_parent2 ON parent2 = p_parent2.pk

This works perfect but when I want to create a VIEW:

CREATE VIEW v_request AS
SELECT * 
FROM request
LEFT JOIN person p_subject ON requestperson = p_subject.pk
LEFT JOIN person p_parent1 ON parent1 = p_parent1.pk
LEFT JOIN person p_parent2 ON parent2 = p_parent2.pk

I get this error: ORA-00957: duplicate column name

I do not want to rename all columns manually. How can I fix this?

Upvotes: 0

Views: 1404

Answers (1)

DKSan
DKSan

Reputation: 4197

Your view would consist of:

  • three columns with the columnname pk
  • three columns with the columnname name (which is not a good columnname)
  • three columns with the columnname sirname

because the tablealiases will not be prepended automatically (which the error ORA-00957: duplicate column name states exactly)

I am quite sure you will have to rename them manually to subject_pk, subject_name, subject_sirname and so on.

Upvotes: 2

Related Questions