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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
