> -----Original Message-----
> From: Alex Arul [mailto:alex.lurthu@stripped]
> Sent: Thursday, April 27, 2006 11:28 PM
> To: Daevid Vincent
> Cc: mysql@stripped
> Subject: Re: Help with subqueries...
>
> 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 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.
Thanks Alex, that got me started. I don't understand why I had to use "IN"
when the example uses "=" but at least it kinda works...
The problem is now that I can't get the right data.
mysql> select max(created_on), user_id, id from logs group by user_id;
+---------------------+---------+----+
| max(created_on) | user_id | id |
+---------------------+---------+----+
| 2006-04-25 20:10:59 | NULL | 4 |
| 2006-04-27 23:48:27 | 1 | 50 | <-- 456
| 2006-04-27 22:18:35 | 2 | 16 | <-- 431
+---------------------+---------+----+
The "max" date is correct but that isn't the correspoinding action id,
they should be 456 and 431 instead...
Which I really don't understand this:
mysql> select max(id), user_id, id from logs group by user_id;
+---------+---------+----+
| max(id) | user_id | id |
+---------+---------+----+
| 183 | NULL | 4 |
| 456 | 1 | 50 |
| 431 | 2 | 16 |
+---------+---------+----+
3 rows in set (0.00 sec)
So that is making this query wrong too of course.
mysql> SELECT max(`logs`.created_on), `logs`.*, users.username FROM `logs`
LEFT JOIN users ON `logs`.user_id = users.id WHERE user_id IN (SELECT id
FROM users ORDER BY username) GROUP BY user_id ORDER BY `logs`.created_on\G
*************************** 1. row ***************************
max(`logs`.created_on): 2006-04-27 22:18:35
id: 16
user_id: 2
created_on: 2006-03-14 22:40:16
body: Logged in.
username: joe
*************************** 2. row ***************************
max(`logs`.created_on): 2006-04-27 23:48:27
id: 50
user_id: 1
created_on: 2006-03-31 16:15:16
body: Created Account
username: bob
