I need help with an SQL Join/results
I have two tables SCHOOLS and STUDENTS They both have a SCHOOLID that can be joined. What I need to see are ALL of the SCHOOLS and fields from the SCHOOLS table and a virtual field telling me if there are ANY STUDENTS that have that SCHOOLID.
Example:
SCHOOLS
SchoolID
School
Active
STUDENTS
name
SchoolID
RESULT Table:
SchoolID Active Used (yes or No)
So the virtual field USED is telling me if there are ANY schools that are in the STUDENTS table.
Then I want to make another query that I can add a where clause to the SchoolID So SAME result be asking "Does SchoolID have any students from the STUDENTS table that have that SchoolID" Then it would list that school (ONE RECORD) SchoolID, School, Active, Used
SELECT Schools.SchoolID, Schools.School, Schools.Active, IIF(Students.SchoolID IS NULL, 'No', 'Yes') AS Used
FROM Schools
LEFT JOIN Students ON Schools.SchoolID = Students.SchoolID;