4

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.

Improve this question
2
  • Maybe you can post the definitions of the tables, including the foreign keys and primary keys. What is the primary key of table 1 for instance? How are table 2 and table 1 related? By Id? Commented Oct 23, 2008 at 19:46
  • All of the tables are related by ID. I think the first thing I need to determine is if an activity was added after the task was closed, then find out who did the activity, what activity they took, when they took it, and what their comment was. Commented Oct 23, 2008 at 20:02

3 Answers 3

Reset to default
7 
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks Bill, I think this one gets the info I need. Unfortunately I am not able to update or make any changes to the database. The task ID is unique in the task table, "when" is unique (actually a timestamp to the milisecond) in the activity table.
OK, if it's a timestamp then it's very unlikely that two people will comment at exactly the same time, and the first query I give above should work. Remember to upvote and select the green checkmark for the answer you think is best. :-)
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.

Improve this answer

1 Comment

Totty's solution gets all comments from a Task that is currently closed, including those that were made prior to the closing of the Task.
0

Are you planning on joining the tables based on the date? So you will only have 1 change per day?

Improve this answer

3 Comments

It looks to me like he is using Task.ID as a primary key and does not have a primary key for the other tables, only using their ID fields as foreigns of Task.ID.
I am planning to find out if a developer acted on a task after it was closed, so I am assuming that the date is the key? An activity date after the close date?
It could be changed multiple times in a day.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.