mySQL在一个查询中从多个表中获取信息

我对SQL非常陌生,在尝试了几个不同的谷歌搜索并阅读了一些SQL教程后,似乎无法获得所需的信息.

我认为它涉及某种类型的连接,但不能直接得到它们.

给出以下示例表:

表1(活动每次对任务进行更改时都会更新,每天可以多次):

ID  Who     What        When
001 John    Created 2008-10-01

001 Bill    Closed  2008-10-02

001 John    Updated 2008-10-03

002 Bill    Created 2008-10-04

002 John    Updated 2008-10-05

002 Bill    Closed  2008-10-06

表2(任务 - 这是主要任务跟踪表):

ID  Created Status
001 2008-10-01  Closed
002 2008-10-04  Closed

表3(评论):

ID  When    Comment


什么SQL(mySQL,如果有什么不同)查询我会用来找出谁已经关闭的任务做了什么？

结果将是这样的:

Who What    ID  When    Comment
Bill Updated 002 2008-10-03 "Nice job"


这意味着Bill在关闭后更改了任务002,并添加了评论"Nice Job"

任何帮助将非常感激.

提前致谢.


1> Bill Karwin..：
SELECT a1.Who, a1.What, a1.ID, c.When, c.Comment
FROM Activity AS a1
  JOIN Activity AS a2 ON (a1.ID = a2.ID AND a1.When > a2.When)
  JOIN Comments AS c ON (a1.ID = c.ID AND a.When = c.When);
WHERE a2.What = 'Closed';


我认为您需要一些方法将Comments中的行与Activity中的正确行相关联.现在,如果有两个人在同一天评论给定的任务,你不知道谁的评论是谁.我建议您为Activity中的每一行指定一个唯一键,然后从Comments表中引用它.

CREATE TABLE Tasks (
  Task_ID      INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Created      DATE NOT NULL,
  Status       VARCHAR(10)
) TYPE=InnoDB;

CREATE TABLE Activity (
  Activity_ID  INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Task_ID      INT NOT NULL REFERENCES Tasks,
  Who          VARCHAR(10) NOT NULL,
  What         VARCHAR(10) NOT NULL,
  When         DATE NOT NULL
) TYPE=InnoDB;

CREATE TABLE Comments (
  Comment_ID   INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  Activity_ID  INT NOT NULL REFERENCES Activity,
  Who          VARCHAR(10) NOT NULL,
  When         DATE NOT NULL,
  Comment      VARCHAR(100) NOT NULL
) TYPE=InnoDB;


然后您可以建立关联,以便查询返回更准确的结果:

SELECT c.Who, a1.What, a1.Task_ID, c.When, c.Comment
FROM Activity AS a1
  JOIN Activity AS a2 ON (a1.Task_ID = a2.Task_ID AND a1.When > a2.When)
  JOIN Comments AS c ON (a1.Activity_ID = c.Activity_ID);
WHERE a2.What = 'Closed';


确保TYPE=InnoDB在MySQL中使用,因为默认存储引擎MyISAM不支持外键引用.