-- View: "viewMovies"

-- DROP VIEW "viewMovies";

CREATE OR REPLACE VIEW "viewMovies" AS
 SELECT "Movies"."Name", "Movies"."Year", "Genres"."Type" AS "Genre", "Formats"."FormatTitle" AS "Format", "Ratings"."RatingTitle" AS "Rating", "Directors"."FirstName" AS "Director FirstName", "Directors"."LastName" AS "Directors LastName", "Actors"."FirstName" AS "Actor FirstName", "Actors"."LastName" AS "Actor LastName", "Comments"."UserComments", "Languages"."Language", "Movies"."PersonalRating"
   FROM "Movies"
   LEFT JOIN "Genres" ON "Movies"."GenreId" = "Genres"."Id"
   LEFT JOIN "Formats" ON "Movies"."FormatId" = "Formats"."Id"
   LEFT JOIN "Ratings" ON "Movies"."RatingId" = "Ratings"."Id"
   LEFT JOIN "Directors" ON "Movies"."DirectorId" = "Directors"."Id"
   LEFT JOIN "Comments" ON "Movies"."CommentsId" = "Comments"."Id"
   LEFT JOIN "Languages" ON "Movies"."LanguagesId" = "Languages"."Id"
   LEFT JOIN "Actors" ON "Movies"."ActorId1" = "Actors"."Id" OR "Movies"."ActorId2" = "Actors"."Id" OR "Movies"."ActorId3" = "Actors"."Id";

ALTER TABLE "viewMovies" OWNER TO postgres;
COMMENT ON VIEW "viewMovies" IS 'Will return all data for a movie by joining all tables in our database';