-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
J S wrote: | | |> J S wrote: |> | Hi, |> | |> | I need a bit of help with a mySQL query. I have a list of users in a |> | text file called 'users': |> | |> | u655354 |> | u687994 |> | u696974 |> | u728141 |> | .. |> | .. |> | |> | and I need to check the last date each user logged on to the proxy in |> | the last 3 months. |> | |> | In my database, there is a table for the users: |> | |> | mysql> desc user_table; |> | +-------+------------------+------+-----+---------+----------------+ |> | | Field | Type | Null | Key | Default | Extra | |> | +-------+------------------+------+-----+---------+----------------+ |> | | ID | int(10) unsigned | | MUL | NULL | auto_increment | |> | | uid | varchar(10) | | PRI | | | |> | +-------+------------------+------+-----+---------+----------------+ |> | 2 rows in set (0.00 sec) |> | |> | and a table (tYYMMDD) for each days log: |> | |> | mysql> desc t20041209; |> | +--------------+----------------------+------+-----+---------+-------+ |> | | Field | Type | Null | Key | Default | Extra | |> | +--------------+----------------------+------+-----+---------+-------+ |> | | uid | int(10) unsigned | | MUL | 0 | | |> | | time | timestamp(14) | YES | | NULL | | |> | | ip | int(10) unsigned | | MUL | 0 | | |> | | urlid | int(10) unsigned | | MUL | 0 | | |> | | timetaken | smallint(5) unsigned | YES | | 0 | | |> | | cs_size | int(10) unsigned | YES | MUL | 0 | | |> | | sc_size | int(10) unsigned | YES | MUL | 0 | | |> | | method_ID | tinyint(3) unsigned | | | 0 | | |> | | action_ID | tinyint(3) unsigned | | | 0 | | |> | | virus_ID | tinyint(3) unsigned | | | 0 | | |> | | useragent_ID | smallint(5) unsigned | | MUL | 0 | | |> | +--------------+----------------------+------+-----+---------+-------+ |> | 11 rows in set (0.00 sec) |> | |> | The time column here gives the actual time the user logged on, but I |> | would be happy just to know the date (which I could get from the table |> | name if the user's uid was present in there). |> | |> | Could anyone help me to write an SQL query to do this please? |> | |> | Many thanks. Please let me know if you need anymore information. |> |> Select u.uid, max(l.time) as lastLog |> ~ From user_table u join t20041209 l on u.uid = l.uid |> ~ Group by uid; |> | | Thanks Mike. I need to run this query over 3 months though. Is there a | quick way to write: | | t20041101 union t20041102 union t20041103 union ....... t20050125 union | t20050126
Not that I know of. Why are you using a different table per day - as opposed to a single log table with a field containing the day? I'm sure there's a reason for the current structure, but having a logday field in a single log table would make this kind of query much easier. A simple BETWEEN clause would suffice if it was in a single table.
As it is, all I can suggest is to JOIN on all 90 tables - and hope MySQL can handle the query - and that you can type all of them without error. Note that if you use a UNION query as you suggest above, you will get the last login FOR EACH DAY - not the overall last loging. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCGhfOjeziQOokQnARAvSUAJ4zyHmYa95o+0eZ2zs//S24n0kyqQCeO6M2 UzELKfj6hZ14bp+NLLj+McQ= =NZBB -----END PGP SIGNATURE-----
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
