Please help me write SQL query
select distinct
f.FacultyName,
(select count(d.DepartmentID) from Department d where d.FacultyId = f.FacultyID) TheNumberOfDepartments
from Faculty f
Thanks!
Оставить комментарий
select distinct
f.FacultyName,
(select count(d.DepartmentID) from Department d where d.FacultyId = f.FacultyID) TheNumberOfDepartments
from Faculty f
Lynx
We have 2 tables with relationship one-manyFaculty (FacultyID, FacultyName) : one side, primary key: FacultyID
Department(DepartmentID, DepartmentName, FacultyID) : many side, primary key DepartmentID
One Faculty may have many Departments
Task: Write SQL with result (FacultyName, TheNumberOfDepartment). Do not use GROUP BY; only use SELECT FROM WHERE and nested SECLECT
P/S: With GROUP BY, we finish the task easily:
SELECT Faculty.FacultyName, Count(Department.DepartmentID) AS TheNumberOfDepartment
FROM Faculty LEFT JOIN Department ON Faculty.FacultyID = Department.FacultyID
GROUP BY Faculty.FacultyID, Faculty.FacultyName