FPDAM2023/MariaDB/FPDAM2023.5.Enrollments/FPDAM2023.5.Enrollments.02.views.my.sql

45 lines
1.5 KiB
SQL

create database if not exists FPDAM2023 character set utf8mb4 collate utf8mb4_general_ci;
use FPDAM2023;
delimiter ;^
drop view if exists EnrollmentsView;^
create view EnrollmentsView as select
enrollments.id as id,
procedures.database_id as database_id,
procedures.id as procedure_id,
procedures.`database` as `database`,
procedures.`procedure` as `procedure`,
sessions.id as session_id,
sessions.user_id as user_id,
sessions.nick as nick,
sessions.ip_id as ip_id,
sessions.ip as ip,
enrollments.`year` as `year`,
types.id as type_id,
types.name as type_name,
partners.id as partner_id,
partners.code as partner_code,
courses.id as course_id,
courses.code as course_code,
courses.name as course_name,
centers.id as center_id,
centers.code as center_code,
centers.name as center_name,
enrollments.score as score,
enrollments.date_in as date_in
from Enrollments enrollments
join AnP.SessionsFullView sessions on enrollments.`session` = sessions.id
join AnP.ProceduresFullView procedures on enrollments.`procedure` = procedures.id
join EnrollmentsTypes types on enrollments.`type` = types.id
join Partners partners on enrollments.partner = partners.id
join Courses courses on enrollments.course = courses.id
join Centers centers on enrollments.center = centers.id
where
types.date_out is null &&
enrollments.date_out is null &&
partners.date_out is null &&
courses.date_out is null &&
centers.date_out is null;^
delimiter ;