mySQL get information from multiple tables in one query

Question

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.

Answer 1

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.

Answer 2

You will need to use a JOIN statement to retrieve fields from multiple tables. http://www.w3schools.com/Sql/sql_join.asp

SELECT Activity.Who, Activity.What, Comments.When, Comments.Comment FROM Activity JOIN Comments ON Activity.ID = Comments.ID JOIN Tasks ON Comments.ID = Tasks.ID WHERE Tasks.Status = 'Closed'

Also, you're table structure looks to have a little redundancy.