Table tblStudents columns: id, name, gender, SchoolName, SchoolId.
Table tblSchool columns: id, name, location.
select name,
(select name
from tblStudents as a
where a.SchoolId=id
limit 1)
from tblSchool;
I am looking to return SchoolName along with the first student enrolled in it.
(That’s why I am using limit 1.)
There might be multiple tblStudents records with the same SchoolId in tblStudents.
Since the id column in the where clause exists in both tables, the id it is using with the query belongs to tblStudents, instead of tblSchool.
I can’t use aliasing with tblSchool.
4
SELECT s.name AS SchoolName,
(SELECT a.name
FROM tblStudents AS a
WHERE a.SchoolId = s.id
LIMIT 1) AS FirstStudentName
FROM tblSchool AS s;
you can’t use aliasing for tblSchool, but need to ensure that your subquery uses the id from tblSchool and not from tblStudents.
4