php - 多表内容选择

Question

我正在研究考试系统。我有 (student),(student_test),(test),(departments) 表彼此有关系。当每个学生登录时，都会有一个名为参加考试的链接，该链接会将他们重定向到参加考试页面。

问题:如果学生没有参加考试，我如何才能从测试表中获取所有与学生部门测试相关的可用信息，或者换句话说，如何从测试表中获取学生未参加的所有测试？

$select=$connection->query("SELECT
  student.std_id,
  student.department,
  test.depart_id,
  test.test_id,
  test.test_name,
  test.test_from,
  std_test.stdid,
  std_test.std_test_id
FROM student
  INNER JOIN test
    ON student.department = test.depart_id
  INNER JOIN std_test
    ON std_test.std_test_id <> test.test_id

我也试过这段代码，但没有结果。

SELECT
  student.std_id,
  student.department,
  test.depart_id,
  test.test_id,
  test.test_name,
  test.test_from,
  std_test.stdid,
  std_test.std_test_id
FROM student
  INNER JOIN test
    ON student.department = test.depart_id
  INNER JOIN std_test
    ON std_test.std_test_id <> test.test_id
WHERE test.test_id <> std_test.std_test_id AND student.std_id <> std_test.stdid

架构脚本

CREATE TABLE student (
  std_id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) NOT NULL,
  f_name varchar(50) NOT NULL,
  department int(11) NOT NULL,
  semester varchar(255) NOT NULL,
  pass varchar(255) NOT NULL,
  email varchar(60) NOT NULL,
  rollnumber varchar(20) NOT NULL,
  PRIMARY KEY (std_id),
  INDEX department (department),
  UNIQUE INDEX email (email),
  CONSTRAINT student_ibfk_1 FOREIGN KEY (department)
  REFERENCES departments (dep_id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AUTO_INCREMENT = 8
AVG_ROW_LENGTH = 8192
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

CREATE TABLE test (
  test_id int(11) NOT NULL AUTO_INCREMENT,
  test_name varchar(80) NOT NULL,
  test_date varchar(30) NOT NULL,
  test_from datetime NOT NULL,
  test_to datetime NOT NULL,
  test_code varchar(30) NOT NULL,
  test_conducter varchar(30) NOT NULL,
  test_duration int(11) NOT NULL,
  total_question int(11) NOT NULL,
  session varchar(50) DEFAULT NULL,
  subject_id int(11) NOT NULL,
  semester_id int(11) NOT NULL,
  depart_id int(11) NOT NULL,
  status varchar(50) NOT NULL,
  PRIMARY KEY (test_id),
  INDEX depart_id (depart_id),
  INDEX semester_id (semester_id),
  INDEX subject_id (subject_id, semester_id, depart_id),
  CONSTRAINT test_ibfk_1 FOREIGN KEY (subject_id)
  REFERENCES subjects (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT test_ibfk_2 FOREIGN KEY (semester_id)
  REFERENCES semester (sem_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT test_ibfk_3 FOREIGN KEY (depart_id)
  REFERENCES departments (dep_id) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = INNODB
AUTO_INCREMENT = 6
AVG_ROW_LENGTH = 16384
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

CREATE TABLE std_test (
  stdid int(11) NOT NULL,
  std_test_id int(11) NOT NULL,
  starttime timestamp DEFAULT CURRENT_TIMESTAMP,
  endtime timestamp DEFAULT '0000-00-00 00:00:00',
  progress enum ('over', 'inprogress') NOT NULL,
  PRIMARY KEY (std_test_id),
  INDEX stdid (stdid),
  INDEX tstid (std_test_id),
  CONSTRAINT std_test_ibfk_2 FOREIGN KEY (std_test_id)
  REFERENCES test (test_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT std_test_ibfk_3 FOREIGN KEY (stdid)
  REFERENCES student (std_id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AVG_ROW_LENGTH = 16384
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

Answer 1

未经测试和缩短，但这是我在阅读您的问题时的理解

SELECT .... 
FROM student s
INNER JOIN test t ON t.depart_id = s.department
LEFT JOIN std_test st ON t.test_id = st.std_test_id
WHERE st.stdid IS NULL

现在应该可以工作了