SQLize Online / PHPize Online  /  SQLtest Online

create table videos (id int); create table category_video (category_id int, video_id int); create table recommendation_profile_categories (category_id int, user_id int, score int); create table theme_video (theme_id int, video_id int); create table recommendation_profile_themes (theme_id int, user_id int, score int); insert into videos values (1); insert into category_video values (1, 1); insert into category_video values (2, 1); insert into recommendation_profile_categories values (1, 1, 10); insert into recommendation_profile_categories values (2, 1, 15); insert into theme_video values (1, 1); insert into theme_video values (2, 1); insert into recommendation_profile_themes values (1, 1, 20); insert into recommendation_profile_themes values (2, 1, 25); select * from videos; select * from category_video; select * from recommendation_profile_categories; select * from theme_video; select * from recommendation_profile_themes; select v.id, case when row_number() over (partition by rpc.score) > 1 then rpc.score end as rpc_score, case when row_number() over (partition by rptms.score) > 1 then rptms.score end as rptms_score from videos v left join category_video cv on cv.video_id = v.id left join recommendation_profile_categories rpc on rpc.category_id = cv.category_id and rpc.user_id = 1 left join theme_video tmv on tmv.video_id = v.id left join recommendation_profile_themes rptms on rptms.theme_id = tmv.theme_id and rptms.user_id = 1 where v.id = 1;