SELECT newest date from table in complex select

[acidbox]

I have two tables. The first table is a users table and the second table is a searches table.

The first table stores information about the user and each user only has 1 row of information.

The second table makes a record each time a user runs a search, and could potentially have many records associated with them.

I would like to build a query that selects the user's name from the users table and the most recent search date from the searches table.

Could someone help me with this? Thanks.

[matt_4013]

Hi there,

I'm assuming you're using a user id to link the tables?

If so, I'd do it something like this (If not, can you post a database schema, so I can help a bit more?)

SELECT u.name FROM users AS u LEFT JOIN searches AS s ON u.user_id=s.user_id ORDER BY UNIX_TIMESTAMP(s.date) DESC LIMIT 0,1

[acidbox]

I am linking the tables by the user id. Your select query works, but it only returns 1 result. I need to find a statement that will return the newest search date for EACH user in the users table.

[acidbox]

I figured it out... For anyone else, the answer is to use MAX()

[matt_4013]

Heh, sorry, I thought you just wanted to return the latest date, with the user name. Anyway, it's just a matter of using the LEFT JOIN properly.