StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>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'; </code></pre> <p>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.</p> <pre><code>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; </code></pre> <p>Then you can make associations so the query returns more accurate results:</p> <pre><code>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'; </code></pre> <p>Make sure to use <code>TYPE=InnoDB</code> in MySQL because the default storage engine MyISAM doesn't support foreign key references.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload