join query with limited results

[mrgrammar]

I have a client table and a surveys table. It is a one-to-many relationship. Each client can have multiple surveys.

I need a list of the clients with the last survey that they took.

$statement = "SELECT c.*, s.qa_date FROM client AS c LEFT JOIN surveys AS s ON c.client_id = s.qa_clienttname ORDER BY s.qa_date";

The query above would select each of the clients, but it would get all the surveys for all the clients. I want just the latest survey date for each client. How would I do that?

[RalphLeMouf]

$statement = "SELECT c.*, s.qa_date FROM client AS c LEFT JOIN surveys AS s ON c.client_id = s.qa_clienttname ORDER BY s.qa_date DESC, s.qa_time DESC";

I'm assuming if you had time it would look like that, if not just use "s.qa_date DESC"

[Lars_Berg]

select c.*, 
       (select max(s.qa_date) 
          from surveys as s 
         where s.client_id = s.qa_clienttname) as lastdate
  from client as c 
 order by lastdate