SQL script problem

[tim55]

I have two tables - 'company' and 'visits', both with a company_id field - and they track visits made by salesmen to various companies. However, since some companies may not have been visited, the following SQL initially gives me all the information I need:

select c.*, v.* from company c
left outer join visits v on
(v.comp_id = c.comp_id)

However, once more than one visit is made, instead of returning multiple lines for a particular company, I want only the latest visit to be included. I have tried a number of ways, including:

select c.*, v.* from company c
left outer join visits v on
(v.comp_id = c.comp_id
and v.date =
(select max(t.date) from visits t
where t.comp_id = b.comp_id))

but although I can get the latest visit, I only get records returned where a company has had a visit - ie. a company has at least one corresponding record in visits.

Can anyone tell me where I am going wrong? Thanks.

 

[r937]

you have a table alias "b" that wasn't defined

try it this way --

select c.*
     , v.* 
  from company c
left outer 
  join visits v 
    on c.comp_id = v.comp_id
   and v.date =
     ( select max(t.date) 
         from visits t
        where t.comp_id = [b][COLOR=red]c[/color][/b].comp_id )

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

 

[tim55]

Thanks r937, but I'm afraid I mislead you. My code did have a correct alias, but I wrote it incorrectly in the post. I have tried again, but it still excludes records with no associated visits entry.