On 4/28/06, Daevid Vincent <daevid@stripped> 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
