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 * FROM logs WHERE users.id = (SELECT id FROM users); > ERROR 1054 (42S22): Unknown column 'users.id' in 'where clause' the table logs has only the column user_id . 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. Thanx Alex
