mySQL get information from multiple tables in one query

database, mysql

Solution

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';

I think you need some way to associate a row in Comments to the correct row in Activity. Right now if two people comment on a given Task on the same day, you don't know whose comment is whose. I'd recommend that you give each row in Activity a unique key, and then reference that from the Comments table.

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;

Then you can make associations so the query returns more accurate results:

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';

Make sure to use `TYPE=InnoDB` in MySQL because the default storage engine MyISAM doesn't support foreign key references.

Problem

I'm terribly new to SQL, and cannot seem to get the desired information out, after trying a few different google searches, and reading through some SQL tutorials. I think it involves some sort of joins, but cannot get them straight. Given the following sample tables: Table 1(Activity This is updated every time a change is made to a task, could be manytimes per day): ``` ID Who What When 001 John Created 2008-10-01<br> 001 Bill Closed 2008-10-02<br> 001 John Updated 2008-10-03<br> 002 Bill Created 2008-10-04<br> 002 John Updated 2008-10-05<br> 002 Bill Closed 2008-10-06<br> ``` Table 2(Tasks - This is the main task tracking table): ``` ID Created Status 001 2008-10-01 Closed 002 2008-10-04 Closed ``` Table 3(Comments): ``` ID When Comment<br 001 2008-10-01 "I'm creating a new task" 001 2008-10-02 "I have completed the task" 001 2008-10-03 "Nice job" 002 2008-10-04 "I'm creating a second task" 002 2008-10-05 "This task looks too easy" 002 2008-10-06 "I have completed this easy task" ``` What SQL (mySQL if it makes any difference) query would I use to find out who had done something on a task that had been closed? The results would be something like: ``` Who What ID When Comment Bill Updated 002 2008-10-03 "Nice job" ``` Meaning that Bill changed task 002 after it was closed, and added the comment "Nice Job" Any help would be much appreciated. Thanks in advance.

Original source