Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – id column in the subquery where clause exists in both tables, the id it is using is from the wrong table

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.

Tags:

2

Answers


  1. 0 
    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.

    Login or Signup to reply.
  2. 0

    Changes you query as follows to get the first Student name:

    SELECT s.name AS SchoolName,
    (   SELECT a.name
        FROM (select * from tblStudents order by id) AS a
        WHERE a.SchoolId = s.id
        LIMIT 1
    ) AS FirstStudentName
FROM tblSchool AS s;

    This will show first student(min of student id) name according to school_id

    Thank you!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search