Get only the oldest entry in a join So I have a table bugs

|bugid| title|

the table is lacking a date column. But when creating a new entry, most likely always a new entry gets created in bugnotes as well saying "bug created" with the date.

bugnotes:

| bugnotesid | bugid | datetime | text |

I want to list all bugs, showing the date of the oldest entry in bugnotes if available
I think that you want something like this (see below for table defs). Something appears to have happened my SQLFiddle demo.

SELECT bs.bugid, bs.bugtitle, bn.bugnotesid, bn.notetime, bn.notetext 
FROM bugs bs, bugnotes bn
JOIN  
(
  SELECT bugnotesid, MIN(notetime) 
  FROM bugnotes 
  GROUP By (bugid)
) mytab
ON bn.bugnotesid = mytab.bugnotesid 
WHERE bs.bugid = bn.bugid;

CREATE TABLE bugs values
(
  bugid int,
  bugtitle varchar(34)
);

INSERT INTO bugs values(1, 'the first bug');
INSERT INTO bugs values(2, 'the second bug');
INSERT INTO bugs values(3, 'the third bug');

CREATE TABLE bugnotes
(
  bugnotesid int,
  bugid int,
  notetime datetime,
  notetext varchar(400)
);

INSERT INTO bugnotes values(1, 1, '2014-03-14', 'afasdfasdf');
INSERT INTO bugnotes values(2, 1, '2014-03-15', 'afasdfasdf');
INSERT INTO bugnotes values(3, 1, '2014-03-16', 'afasdfasdf');
INSERT INTO bugnotes values(4, 1, '2014-03-17', 'afasdfasdf');
INSERT INTO bugnotes values(5, 2, '2014-02-14', 'afasdfasdf');
INSERT INTO bugnotes values(6, 2, '2014-02-20', 'afasdfasdf');
INSERT INTO bugnotes values(7, 2, '2014-02-22', 'afasdfasdf');
INSERT INTO bugnotes values(8, 2, '2014-02-25', 'afasdfasdf');
INSERT INTO bugnotes values(9, 2, '2014-02-28', 'afasdfasdf');
INSERT INTO bugnotes values(10, 3, '2013-03-14', 'afasdfasdf');
INSERT INTO bugnotes values(11, 3, '2013-03-16', 'afasdfasdf');
INSERT INTO bugnotes values(12, 3, '2013-03-19', 'afasdfasdf');
INSERT INTO bugnotes values(13, 3, '2013-03-20', 'afasdfasdf');
INSERT INTO bugnotes values(14, 3, '2013-03-27', 'afasdfasdf');


[EDIT - after profiling the two queries offered as solutions here]

This bit is really a question to Joshua - not sure if I should make it a new post or keep going here - I can always move it if requested.

Hi Joshua,

I'm sort of starting with MySQL so I took advantage of our exchange to profile our two responses to the OP. I'm a bit puzzled by what I found. Mine takes ~ 200 us, whereas yours appears to take more than double that - and there are far more rows in yours (more info?). I'm curious as to why? Do you know what's going on.

I hasten to add that I did a RESET QUERY CACHE before running the second (mine) query. I also ran them in reverse order and obtained the same result. 

I'm baffled!

Joshua's query.

mysql&gt; show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000186 |
| checking permissions | 0.000011 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000043 |
| init                 | 0.000069 |
| System lock          | 0.000019 |
| optimizing           | 0.000012 |
| statistics           | 0.000038 |
| preparing            | 0.000057 |
| Creating tmp table   | 0.000056 |
| Sorting result       | 0.000008 |
| executing            | 0.000006 |
| Sending data         | 0.000218 |
| Creating sort index  | 0.000060 |
| end                  | 0.000011 |
| removing tmp table   | 0.000029 |
| end                  | 0.000009 |
| query end            | 0.000013 |
| closing tables       | 0.000018 |
| freeing items        | 0.000027 |
| cleaning up          | 0.000024 |
+----------------------+----------+
21 rows in set, 1 warning (0.00 sec)

mysql&gt; reset query cache;
Query OK, 0 rows affected (0.00 sec)


My query.

mysql&gt; show profile for query 2;
+----------------+----------+
| Status         | Duration |
+----------------+----------+
| starting       | 0.000131 |
| query end      | 0.000010 |
| closing tables | 0.000007 |
| freeing items  | 0.000020 |
| cleaning up    | 0.000031 |
+----------------+----------+
5 rows in set, 1 warning (0.00 sec)

mysql&gt;