You need to use a join. Specifically, a left outer join. I won't explain this in great detail; figuring out how this works can be your research topic ;) but try this:
select a.id, a.from, a.to, a.message, a.insertdate, b.insertdate from messages as a left outer join messages as b on a.id = b.id where a.status='SENT'; On Tue, Dec 23, 2008 at 7:28 PM, sangprabv <sangpr...@gmail.com> wrote: > Hi, > I have a table which stores log traffic. The table contains these > fields: > transaction_id, from, to, message, status, insertdate > For example there is a message from A send to B, when the message sent > to B it will insert new record. And when the message is read by B, it > will also insert new record. > So the records should be something like this: > transaction_id, from, to, message, status, insertdate > 20081224001, A, B, stest, SENT, 2008-12-24 01:01:01 > 20081224001, A, B, NULL, READ, 2008-12-24 01:01:03 > My question is if I want to lookup 20081224001 and expect the result to > be like this: > transaction id, from, to, message, sent, received > 20081224001, A, B, stest, 2008-12-24 01:01:01, 2008-12-24 01:01:03 > How to build the query then? Please help and TIA. > > > Willy > Soap and education are not as sudden as a massacre, but they are more > deadly in the long run. -- Mark Twain > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=ba...@xaprb.com > > -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
