> -----Original Message----- > From: Alex Arul [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 27, 2006 11:28 PM > To: Daevid Vincent > Cc: mysql@lists.mysql.com > Subject: Re: Help with subqueries... > > On 4/28/06, Daevid Vincent <[EMAIL PROTECTED]> wrote: > > > > vmware reviewit # mysql --version > > mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) > using readline > > 5.1 > > > > Given two tables: > > > > CREATE TABLE `logs` ( > > `id` bigint(20) unsigned NOT NULL auto_increment, > > `user_id` int(10) unsigned default '0', > > `created_on` timestamp NOT NULL default > > CURRENT_TIMESTAMP on update > CURRENT_TIMESTAMP, > > `body` text, > > PRIMARY KEY (`id`), > > ) ENGINE=InnoDB; > > > > CREATE TABLE `users` ( > > `id` int(10) unsigned NOT NULL auto_increment, > > `username` varchar(30) NOT NULL, > > `firstname` varchar(255) NOT NULL, > > `lastname` varchar(255) NOT NULL, > > `login_date` datetime default NULL, > > `timestamp` timestamp NOT NULL default > > CURRENT_TIMESTAMP on update > CURRENT_TIMESTAMP, > > PRIMARY KEY (`id`), > > ) ENGINE=InnoDB; > > > > > > I want a query that will show me the username and the > single most current > > log body and date for every user. > > > > I think I need to use a sub query, but I can't even get the > very basic > > example one to work... > > http://dev.mysql.com/doc/refman/5.0/en/subqueries.html > > > > mysql> SELECT * FROM logs WHERE user_id = (SELECT id FROM users); > > ERROR 1242 (21000): Subquery returns more than 1 row > > > try SELECT * FROM logs WHERE user_id in (SELECT id FROM users); > > mysql> SELECT logs.*, users.username > > -> FROM logs JOIN users ON users.id = logs.id > > -> WHERE user_id = (SELECT id FROM users ORDER BY username); > > ERROR 1242 (21000): Subquery returns more than 1 row > > > again replace = by in. > > Hope this helps.
Thanks Alex, that got me started. I don't understand why I had to use "IN" when the example uses "=" but at least it kinda works... The problem is now that I can't get the right data. mysql> select max(created_on), user_id, id from logs group by user_id; +---------------------+---------+----+ | max(created_on) | user_id | id | +---------------------+---------+----+ | 2006-04-25 20:10:59 | NULL | 4 | | 2006-04-27 23:48:27 | 1 | 50 | <-- 456 | 2006-04-27 22:18:35 | 2 | 16 | <-- 431 +---------------------+---------+----+ The "max" date is correct but that isn't the correspoinding action id, they should be 456 and 431 instead... Which I really don't understand this: mysql> select max(id), user_id, id from logs group by user_id; +---------+---------+----+ | max(id) | user_id | id | +---------+---------+----+ | 183 | NULL | 4 | | 456 | 1 | 50 | | 431 | 2 | 16 | +---------+---------+----+ 3 rows in set (0.00 sec) So that is making this query wrong too of course. mysql> SELECT max(`logs`.created_on), `logs`.*, users.username FROM `logs` LEFT JOIN users ON `logs`.user_id = users.id WHERE user_id IN (SELECT id FROM users ORDER BY username) GROUP BY user_id ORDER BY `logs`.created_on\G *************************** 1. row *************************** max(`logs`.created_on): 2006-04-27 22:18:35 id: 16 user_id: 2 created_on: 2006-03-14 22:40:16 body: Logged in. username: joe *************************** 2. row *************************** max(`logs`.created_on): 2006-04-27 23:48:27 id: 50 user_id: 1 created_on: 2006-03-31 16:15:16 body: Created Account username: bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]