LendingPolicy SQL

LendingPolicyMapper.xml

select 
			max(library.name) as libraryName,
			libraryId, 
			max(tv_rating_system) as tv_rating_system, 
			max(movie_rating_system) as movie_rating_system,
			max(ab_enabled) as ab_enabled, 
			max(movie_enabled) as movie_enabled, 
			max(music_enabled) as music_enabled, 
			max(tv_enabled) as tv_enabled 
		from (
			select 
				libraryId, kind, 
				tv_rating_system, movie_rating_system,
				case when kind = 'AUDIOBOOK' and enabled then 1 else 0 end as ab_enabled,
				case when kind = 'MOVIE' and enabled then 1 else 0 end movie_enabled,
				case when kind = 'MUSIC' and enabled then 1 else 0 end as music_enabled,
				case when kind = 'TELEVISION' and enabled then 1 else 0 end as tv_enabled,
				case when kind = 'MOVIE' and restrictRating then
								select Rating.name from Rating
								join LendingPolicy on LendingPolicy.maxRating=Rating.id
						end as maxRating
			from (
				select 
					library.id as libraryId, kind.name as kind, lp.enabled,
					ratingsystem_tv.name as tv_rating_system, ratingsystem_movie.name as movie_rating_system
				from lendingpolicy lp 
				join library on lp.libraryid = library.id
				left join ratingsystem ratingsystem_tv on library.tvratingsystemid = ratingsystem_tv.id
				left join ratingsystem ratingsystem_movie on library.movieratingsystemid = ratingsystem_movie.id
				join kind on lp.kindid = kind.id
			) lp
		) flat_results 
		join library on flat_results.libraryId = library.id