45 lines
1.5 KiB
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 ; |