在三个表中选择mysql数据，哪些表属于，哪些不属于？

Question

我有以下表格：

课程

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| c_id        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| c_name      | varchar(255) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

文章

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| a_id        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| a_name      | varchar(255) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Course_Articles

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| ca_id       | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| a_id        | bigint(20)   | NO   |     | NULL    |                |
| c_id        | bigint(20)   | NO   |     | NULL    |                |
| t_id        | bigint(20)   | NO   |     | NULL    |                |
| sort_order  | int(11)      | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

学期(或学期)

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| t_id        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| t_name      | varchar(255) | NO   |     | NULL    |                |
| c_id        | bigint(20)   | NO   |     | NULL    |                |
| sort_order  | int(11)      | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

我需要从这些表格中提供以下数据：

课程名称：现代采气

文章：

• 简介
• 气体萃取技术综述
• 术语1:常规技术

- Coal Mine Methane
- Another conventional technique

​

• 一词之外的另一篇文章
• 另一个
• 术语2:非常规技术

- Underground Coal Gasification
- Coal Bed Methane
- Hydraulic Fracturing

​

• 结论

我知道这些数据可能会从嵌套/树中受益。但这是我必须要做的。正如你所看到的，文章可以属于一个术语，也可以是独立的。

我有点困惑如何有效地查询上面的输出，如本例所示。

<?php
// UPDATED with UNION as suggested
try {
$stmt = $dbh->prepare(
    "SELECT ca . * , a.a_name, t.t_name
FROM Course_Article AS ca 
LEFT JOIN Article AS a ON a.a_id = ca.ca_id
LEFT OUTER JOIN Term AS t ON t.t_id = ca.t_id
WHERE ca.c_id = '2'
UNION SELECT te.t_name, te.t_id, te.c_id
FROM Term AS te
WHERE te.t_id = '2'");

$last_term_id = -1;

$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  if ($last_term_id != $row['t_id']) {
    echo "<b>" . $row['t_name'] . "</b><br />";
    $last_term_id = $row['t_id'];
  }
  echo $row['a_name'] . "<br />";
  }
 } catch(PDOException $e) {
  echo 'Error : '. $e->getMessage();
  exit();
  }
?>

谢谢

Answer 1

因此，这里有一个关于如何结合的想法:第一部分获得所有术语(也包括空的)和它们下面的文章，第二部分得到所有属于本课程但不属于任何术语的附加文章。希望我在这里没有包含太多的逻辑/错误。

select t.t_name, ca.*, a.a_name
from Term t
left outer join Course_Article ca ON ca.t_id = t.t_id
left outer join Article a ON a.a_id = ca.a_id
where t.c_id = '2'

UNION

select null as t_name, ca.*, a.a_name
from Course_Articles ca
left outer join Article a ON a.a_id = ca.a_id
where ca.c_id = '2' and ca.t_id is null

此外，如果您需要对结果排序一点(如果排序顺序在课程文章和学期之间是通用的，则不知道)，则可以对其进行一些扩展(将所需的任何列添加到主选择中以获得最终结果)：

select t_name, a_name, sort_column
FROM
(
    select t.t_name, ca.*, a.a_name, t.sort_order as sort_column
    from Term t
    left outer join Course_Article ca ON ca.t_id = t.t_id
    left outer join Article a ON a.a_id = ca.a_id
    where t.c_id = '2'

    UNION

    select null as t_name, ca.*, a.a_name, ca.sort_order as sort_column
    from Course_Articles ca
    left outer join Article a ON a.a_id = ca.a_id
    where ca.c_id = '2' and ca.t_id is null
) dt
order by sort_column ASC