DROP VIEW IF EXISTS report_translators_view;
CREATE VIEW report_translators_view (
    id,
    full_name,
    alias,
    gender,
    native_language,
    target_language,
    country,
    city,
    education_level,
    specialization,
    experience,
    current_status)
as 
SELECT user.id, CONCAT(user.first_name_user, ' ', user.last_name_user) as full_name, user.alias, user.gender,
native_languages.language AS native_language, target_languages.language AS target_language, countries.country, 
user.city_user as city, education_level.education_level, specialization_list.specialization,
experience.experience, CASE WHEN user.registration_step <> 10 THEN 5 ELSE user.status END as current_status 
FROM user 
LEFT JOIN user_lang ON user.id = user_lang.user_id 
LEFT JOIN languages native_languages ON user_lang.lang_id = native_languages.id 
LEFT JOIN user_translator_languages ON user.id = user_translator_languages.user_id 
LEFT JOIN languages target_languages ON user_translator_languages.target_lang_id = target_languages.id 
LEFT JOIN countries ON user.birth_country_id = countries.id 
LEFT JOIN education_level ON user.edu_level_id = education_level.id 
LEFT JOIN specialization_list ON user.specialization_id = specialization_list.id 
LEFT JOIN user_experience ON user.id = user_experience.user_id 
LEFT JOIN experience ON user_experience.experience_id = experience.id 
LEFT JOIN user_lists ON user.id = user_lists.user_id 
LEFT JOIN roles ON user.role_id = roles.id WHERE (user.role_id=4) 
AND (user.registration_step = 10) 
AND ((user.status='10') AND (user.registration_step = 10)) GROUP BY user.id;