List:General Discussion« Previous MessageNext Message »
From:Alex Arul Date:April 28 2006 6:28am
Subject:Re: Help with subqueries...
View as plain text  
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

Thread
Help with subqueries...Daevid Vincent28 Apr
Re: Help with subqueries...Alex Arul28 Apr
  • RE: Help with subqueries... MAX() and GROUP BYDaevid Vincent28 Apr
RE: Help with subqueries... MAX() and GROUP BYDaevid Vincent28 Apr
RE: Help with subqueries... MAX() and GROUP BYPat Adams28 Apr