Trouble with my SQL join/group by/cursor question

Question

Hi,

I've made database design for a small CRM system. It comprises of Companies and Meetings (amongst others).

Companies has the fields: ID (primary, auto_inc) Name (text)

Meetings has the fields: ID (primary, auto_inc) CompanyId (link to Companies.ID) WhenTime (datetime, to store when the meeting was) Notes (text about the meeting)

What I want to accomplish is a query that gives me a list of all Companies (all fields in the table), AND the WhenTime and Notes of the latest meeting with that company (latest is max(WhenTime), and if there is none, a NULL will do fine).

I think I can solve this with cursors, but I'm afraid of speed.

I've tried several Group By formulations, but I fear I lack the finesse required.

My last attempt was this:

select Companies.ID, Companies.name, mts.whentime, mts.notes
from Companies
left outer join (
    select top(1) *
    from Meetings
    order by [whentime] desc
) mts
on Companies.ID = mts.companyID
order by Companies.name asc

but this code only takes one tuple from Meetings, not one per company in the join, so it's no good.

Any ideas?

Answer 1

Try:

select Companies.ID, Companies.name, mts.whentime, mts.notes
from Companies
cross apply 
(
    select top(1) *
    from Meetings
    where Companies.ID = Meetings.companyID
    order by [whentime] desc
) mts
order by Companies.name asc;

Answer 2

I would start by creating a view of the latest meetings as I find creating views makes complex queries easier to read and maintain and can introduce an element of reusability (if done right).

CREATE VIEW [dbo].[LatestCompanyNotes]
AS

SELECT [CompanyId], [WhenTime], [Notes]
FROM [Meetings] AS M1
INNER JOIN 
    (
        SELECT [CompanyId], MAX([Id]) AS [MaxId]
        FROM [Meetings]
        GROUP BY [CompanyId]
    ) AS M2 ON M2.[CompanyId] = M1.[CompanyId] AND M2.[MaxId] = M1.[Id]

Now you should be able to join to this view in your query as you've previously done.

SELECT Companies.[ID], Companies.[Name], mts.[WhenTime], mts.[Notes]
FROM [Companies]
    LEFT OUTER JOIN [dbo].[LatestCompanyNotes] AS mts ON mts.[CompanyId] = Companies.[ID]
ORDER BY Companies.[Name] ASC

Please note that I've not tested the code (I don't even have SQL Server installed) and it may require a few small changes to work.

Answer 3

You don't need a cross-apply here, just a correlated sub-query to find the most recent meeting date:

SELECT Companies.ID, Companies.name, mts.whentime, mts.notes
FROM Companies
LEFT OUTER JOIN Meetings mts
ON Companies.ID = mts.companyID
AND mts.WhenTime =
 (SELECT MAX(WhenTime) FROM Meetings mtshist WHERE mtshist.companyID = mts.companyID)
ORDER BY Companies.name

Note that this will retrieve all companies, including those which have never had a meeting:

1 Alexander and co.   2010-01-04 some more notes
2 Barnard Partnership 2010-01-03 NULL
3 Collingwood Ltd.    2010-01-07 recent meeting
4 Dimitri and sons    NULL       NULL