SQL problem

[tim55]

I have two tables in a master/detail relationship. Is it possible to use a query to join them so that it returns all of the master records and the LAST entry of any detail records associated with it, determined by date?

This would answer the question 'show me all our salesmen, along with details of the last time they visited a customer', assuming that your detail table kept visit info.

I can use MAX() to return just the detail table Date, but I want the rest of the detail table fields as well. I suspect that I could use nested queries, but my version of MySQL does not support them.

Thanks.

 

[dalchri]

The next best thing is to create a temporary table, join on it, and then drop it when you are done. This will simulate a subquery.

 

[r937]

you can do this without a temp table

select s.name
     , v1.customercompany
     , v1.customercontact
     , v1.datevisited
  from salespeople as s
inner
  join customervisits v1
    on s.id = v1.salesperson_id
inner
  join customervisits v2
    on s.id = v2.salesperson_id
group
    by s.name
     , v1.customercompany
     , v1.customercontact
     , v1.datevisited
having v1.datevisited
     = max(v2.datevisited)

the mysql manual says that most queries that use subqueries can be rewritten as joins, and it's right ;-)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)