View Join Two Tables

Question

I'm trying to create this view on postgresql and I'm gettting the below error:

Create a view named: FacultyCourses: Join the Faculties, Courses tables using the faculty_id and display the values for the following variables: faculty_id, name (Faculties), level (Faculties), course_id, description (Courses), level (Courses).

CREATE VIEW FacultyCourses AS
SELECT faculty_id, name, level, course_id, description,level
FROM faculties, courses;

SELECT * FROM FacultyCourses;

ERROR: ERROR: column reference "level" is ambiguous LINE 2: SELECT faculty_id, name, level, course_id, description,level ^ SQL state: 42702 Character: 56

Thank you all.

Answer 1

Column level is ambiguous, because it exists in both tables: qualify it with the table it belongs to - along with all other column (this is a best practice).

Also, your query is obviously needing a join condition between the tables.

So:

create view facultycourses as
select faculty_id, f.name, f.level, c.course_id, c.description, c.level
from faculties f
inner join courses c using (facility_id);

The using() clause for joins unambiguously resolves the column name - so there is no nned to prefix it in the select clause.