CREATE VIEW movielist
AS
SELECT
        movie.*,
        CASE WHEN movie.SortTitle IS NOT '' THEN movie.SortTitle ELSE movie.Title END AS SortedTitle,
        source.strName AS Source,
        banner.url AS BannerPath,
        clearart.url AS ClearArtPath,
        clearlogo.url AS ClearLogoPath,
        discart.url AS DiscArtPath,
        fanart.url AS FanartPath,
        keyart.url AS KeyartPath,
        landscape.url AS LandscapePath,
        poster.url AS PosterPath,
        GROUP_CONCAT(DISTINCT countries.strCountry) AS Country,
        GROUP_CONCAT(DISTINCT credits.strActor) AS Credits,
        GROUP_CONCAT(DISTINCT directors.strActor) AS Director,
        GROUP_CONCAT(DISTINCT genres.strGenre) AS Genre,
        GROUP_CONCAT(DISTINCT studios.strStudio) AS Studio,
        GROUP_CONCAT(DISTINCT tags.strTag) AS 'Tag'
        FROM
        movie
        LEFT OUTER JOIN moviesource AS source ON (source.idSource = movie.idSource)
        LEFT OUTER JOIN art AS banner ON (banner.media_id = movie.idMovie)
        AND (banner.media_type = 'movie')
        AND (banner.type = 'banner')
        LEFT OUTER JOIN art AS clearart ON (clearart.media_id = movie.idMovie)
        AND (clearart.media_type = 'movie')
        AND (clearart.type = 'clearart')
        LEFT OUTER JOIN art AS clearlogo ON (clearlogo.media_id = movie.idMovie)
        AND (clearlogo.media_type = 'movie')
        AND (clearlogo.type = 'clearlogo')
        LEFT OUTER JOIN art AS discart ON (discart.media_id = movie.idMovie)
        AND (discart.media_type = 'movie')
        AND (discart.type = 'discart')
        LEFT OUTER JOIN art AS fanart ON (fanart.media_id = movie.idMovie)
        AND (fanart.media_type = 'movie')
        AND (fanart.type = 'fanart')
        LEFT OUTER JOIN art AS keyart ON (keyart.media_id = movie.idMovie)
        AND (keyart.media_type = 'movie')
        AND (keyart.type = 'keyart')
        LEFT OUTER JOIN art AS landscape ON (landscape.media_id = movie.idMovie)
        AND (landscape.media_type = 'movie')
        AND (landscape.type = 'landscape')
        LEFT OUTER JOIN art AS poster ON (poster.media_id = movie.idMovie)
        AND (poster.media_type = 'movie')
        AND (poster.type = 'poster')
        LEFT OUTER JOIN countrylinkmovie AS countrylink ON (countrylink.idMovie = movie.idMovie)
        LEFT OUTER JOIN country AS countries ON (countries.idCountry = countrylink.idCountry)
        LEFT OUTER JOIN writerlinkmovie AS writerlink ON (writerlink.idMovie = movie.idMovie)
        LEFT OUTER JOIN actors AS credits ON (credits.idActor = writerlink.idWriter)
        LEFT OUTER JOIN directorlinkmovie AS directorlink ON (directorlink.idMovie = movie.idMovie)
        LEFT OUTER JOIN actors AS directors ON (directors.idActor = directorlink.idDirector)
        LEFT OUTER JOIN genrelinkmovie AS genrelink ON (genrelink.idMovie = movie.idMovie)
        LEFT OUTER JOIN genre AS genres ON (genres.idGenre = genrelink.idGenre)
        LEFT OUTER JOIN studiolinkmovie AS studiolink ON (studiolink.idMovie = movie.idMovie)
        LEFT OUTER JOIN studio AS studios ON (studios.idStudio = studiolink.idStudio)
        LEFT OUTER JOIN taglinks AS taglink ON (taglink.idMedia = movie.idMovie)
        AND (taglink.media_type = 'movie')
        LEFT OUTER JOIN tag AS tags ON (tags.idTag = taglink.idTag)
        GROUP BY movie.idMovie;