如何从多个表中选择数据？

Question

我有 4 个表，我对选择数据感到非常困惑。

的 4 个表中有此字段

t1 -> student_id
  given_name
  user_name

t2 -> school_year
  sem
  class
  student_id
  main_id

t3 -> subject_id
  main_id

t4 -> subject_id
  subject_name

我需要选择 （given_name、subject_name、class 和 main_id）。如何？

我尝试这样

SELECT t2.school_year, t2.rp_main_sem, t2.rp_main_class,  t4.name, t4.subject_id, t1.given_name, t1.user_name FROM t1, t2, t3, t4 WHERE t2.school_year = 2011 AND t2.sem = 1 AND t2.class = 'ES3A' AND t3.subject_id = t4.subject_id AND t1.student_id = t2.rp_main_student_id

Answer 1

   SELECT
      t1.given_name,
      t3.subject_name,
      t2.class,
      t2.main_id 
   FROM
      t1 
   JOIN
      t2 
         ON t1.student_id = t2.student_id 
   JOIN
      t3 
         ON t2.main_id = t3.main_id 
   JOIN
      t4 
         ON t3.subject_id = t4.subject_ID

这应该可以做到，但请告诉我们您需要什么形式的数据。您想获取每个学生的课程吗？或每个班级的学生人数？

Answer 2

您将需要加入表格。每个表都应该有一个主键（或唯一键）以及对其他表的引用（所谓的外键）。

SELECT given_name , t4.subject_name, t2.class, t3.main_id
FROM t1 
JOIN t2 
ON t1.student_id = t2.student_id
JOIN t3
ON t2.main_id = t3.main_id
JOIN t4
ON t2.subject_id = t3.subject_id
WHERE t2.school_year = "2011""  and t2.sem = 1 --added where because of your comment

因此，我们假设

• 学生表 t1 具有 PK student_id
• 科目表 t4 具有 PK subject_id
• t2 是一个关联表，用于链接学生（使用他们的
  student_id）到具有 main_id 的类。
• t3 是一个将主题链接到类的关联表。

Answer 3

SELECT table_name1.given_name,table_name4.subject_name,
table_name2.class,table_name2.main_id
FROM table_name1
INNER JOIN table_name2
ON table_name1.studen_id=table_name2.studen_id
INNER JOIN table_name3
ON table_name2.main_id=table_name3.main_id
INNER JOIN table_name4
ON table_name3.subject_id=table_name4.subject_id

Answer 4

尝试：

   SELECT t1.given_name, t4.subject_name, t2.class, t2.main_id
     FROM t1
LEFT JOIN t2
       ON t1.student_id = t2.student_id
LEFT JOIN t3
       ON t2.main_id = t3.main_id
LEFT JOIN t4
       ON t3.subject_id = t4.subject_id
    WHERE t2.school_year = 2011 
      AND t2.sem = 1

Answer 5

select t1.given_name, t4.subject_name, t2.class, t2.main_id
  from t1 
  join t2 on t1.student_id = t2.student_id
  join t2.main_id = t3.main_id
  join t3.subject_id = t4.subject_id

Answer 6

我想你需要这样的查询：

SELECT t1.given_name, t4.subject_name, t2.main_id
FROM t1
LEFT JOIN t2 ON t1.student_id = t2.student_id
LEFT JOIN t3 ON t2.main_id = t3.main_id
LEFT JOIN t4 ON t3.subject_id = t4.subject_id