
USE madrasha;

-- madina@gmail.com
-- darussalam@gmail.com
SELECT selfstudents.class_name, selfstudents.student_name, madrashas.name FROM selfstudents
JOIN madrashas ON madrashas.email = selfstudents.madrasha_email WHERE selfstudents.madrasha_email = "darussalam@gmail.com";



SELECT 
        m.name AS madrasha_name, 
        s.*, 
        a.*, 
        o.* 
    FROM selfstudents s
    JOIN madrashas m ON s.madrasha_email = m.email
    LEFT JOIN admissions a ON s.form_no = a.form_no
    LEFT JOIN officeadmissions o ON s.form_no = o.form_no
    WHERE s.form_no = "9426067711";



SELECT 
    c.name AS class_name, 
    s.student_name, 
    m.name AS madrasha_name
FROM selfstudents s
JOIN classes c ON s.class_id = c.classes_id
JOIN madrashas m ON s.madrasha_email = m.email
WHERE s.class_id = '9';



SELECT 
    m.email AS madrasha_email, 
    c.classes_id, 
    c.name AS class_name, 
    b.subject_name
FROM booklist b
JOIN classes c ON b.class_id = c.classes_id
JOIN madrashas m ON b.madrasha_email = m.email
WHERE m.email = 'darussalam@gmail' AND c.classes_id = 7 AND class_name = 'মেশকাত'; 




SELECT e.class_name, e.month_year, e.exam_name, COUNT(s.id) AS total_subjects
FROM exam_details e
JOIN subjects s ON e.id = s.exam_id
GROUP BY e.class_name, e.month_year, e.exam_name;


SELECT 
    e.class_name, 
    e.month_year, 
    e.exam_name, 
    GROUP_CONCAT(s.subject_name ORDER BY s.subject_name SEPARATOR ', ') AS subjects_list
FROM exam_details e
JOIN subjects s ON e.id = s.exam_id
GROUP BY e.class_name, e.month_year, e.exam_name;



SELECT name FROM teacher WHERE madrasha_email = 'darussalam@gmail.com'
UNION
SELECT name FROM servants WHERE madrasha_email = 'darussalam@gmail.com';
