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

mysql> SELECT * FROM logs WHERE users.id = (SELECT id FROM users);
ERROR 1054 (42S22): Unknown column 'users.id' in 'where clause'

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

Duh. Isn't that the point? 
Of course I want the subquery to return more than one row?! 
I don't get it.


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