MySQL查询：生成与当前用户在同一所在学校的同一班级中的所有人员

Question

I'm attempting to grab results from 2 tables, one that has the following structure:

CREATE TABLE `users` 
(
    `id` bigint (20) NOT NULL AUTO_INCREMENT , 
    `firstname` varchar (255) NOT NULL, 
    `lastname` varchar (255) NOT NULL, 
    `sex` varchar (255) NOT NULL, 
    `orientation` varchar (50), 
    `email` varchar (255) NOT NULL, 
    `altemail` varchar (255), 
    `username` varchar (255) NOT NULL, 
    `password` varchar (255) NOT NULL, 
    `school` varchar (255) NOT NULL, 
    `age` date NOT NULL, 
    `image1` varchar (255), 
    `image2` varchar (255), 
    `image3` varchar (255), 
    `image4` varchar (255), 
    `image5` varchar (255), 
    `music` longtext, 
    `movies` longtext, 
    `tv` longtext, 
    `books` longtext, 
    `saysomething` longtext, 
    `course` varchar (255), 
    `YEAR` integer (11), 
    `verified` varchar (255), 
    `verifieddatetime` datetime, 
    `string` varchar (255), 
    `alerts` varchar (50),
    PRIMARY KEY (`id`)
) 

And one that has the following structure:

CREATE TABLE `timetable` 
(
    `id` bigint (20) NOT NULL AUTO_INCREMENT , 
    `studentid` varchar (255), 
    `classno` varchar (50), 
    `classname` varchar (255), 
    `classnumber` varchar (255), 
    `prof` varchar (255),
    PRIMARY KEY (`id`)
)

The username is stored as a foreign key in the timetable table. Each student enters in their courses in the timetable table. Attempting to do a search results that would generate all the people that are in the same classes at the same school as the current user. Need to return the id, firstname, lastname,image1 fields from the users table and the classname, classnumber of the timetable table. I have the following query to grab results from the users at the same school:

$query = "select users.id, users.firstname, users.lastname, users.age, users.image1, users.school, category.category from users, category where users.image1 !='male.gif' and users.image1 !='female.gif' and users.verified ='yes' and users.username !='$_SESSION[myusername]' and users.school = '$school' and users.school = category.cat_id"; 

But I cannot figure out how to grab the classes as well.

Answer 1

SELECT
  users.id,
  users.firstname,
  users.lastname,
  users.age, 
  users.image1,
  users.school,
  category.category,
  timetable.classname,
  timetable.classnumber
FROM
  users
INNER JOIN
  category
ON
  users.school = category.cat_id
INNER JOIN
  timetable
ON
  timetable.studentid = users.id
INNER JOIN
  (
  SELECT
    classno
  FROM
    timetable
  WHERE
    studentid = $id_of_the_user
  ) classes_of_the_one_user
ON
  classes_of_the_one_user.classno = timetable.classno
WHERE
  users.image1 != 'male.gif'
AND
  users.image1 != 'female.gif'
AND
  users.verified = 'yes'
AND
  users.school = '$school'