I have 3 tables with the following fields: **tblCustomer:** idCustomer, Filenr, Name, Address **tblStatusPerCustomer:** idStatusPerCustomer, idCustomer, idStatusDescription, DateLastmodification **tblStatusDescription:** idStatusDescription, StatusDescription **Relations:** tblCustomer.idCustomer > tblStatusPerCustomer.idCustomer tblStatusPerCustomer.idStatusDescription > tblStatusDescription.idStatusDescription **Required return values:** tblCustomer.idCustomer, tblCustomer.Filenr, tblCustomer.Name, tblStatusPerCustomer.DateLastmodification, tblStatusDescription.StatusDescription WHERE tblStatusPerCustomer.DateLastmodification = MAX(tblStatusPerCustomer.DateLastmodification) Tested query: SELECT DISTINCT TOP (100) PERCENT cl.Name, sc.StatusDescription , MAX(sc.DateLastmodification) AS MaxDate FROM StatusPerCustomer AS spc INNER JOIN ( SELECT tblStatusDescription.StatusDescription, MAX(tblStatusPerCustomer.DateLastmodification) AS DateLastModification, tblStatusPerCustomer.idCustomer FROM StatusPerCustomer LEFT OUTER JOIN tblStatusDescription ON StatusPerCustomer.Status = tblStatusDescription.idStatusDescription GROUP BY tblStatusDescription.StatusDescription, StatusPerCustomer.idCustomer) AS sc ON sc.idCustomer = spc.idCustomer INNER JOIN tblCustomer AS cl ON cl.idCustomer = spc.idCustomer GROUP BY cl.Filenr, cl.Name, sc.StatusDescription, sc.DateLastModification ORDER BY cl.Name, MaxDate DESC, cl.Filenr, sc.StatusDescription Resultset returns ALL previous Statusdescriptions instead of only the last Status per customer added to tblStatusPerCustomer What am I doing wrong here?
<\!-- **Begin Edit** My apology was in order and I have already offered it to Berend. What I said did not come out right. I should have dissected the query and say something like the following: There are some problems with the original query in question. First question was raised by Steve Jones: > Is there really a status for a > customer (StatusPerCustomer) that > doesn't have a matching status? What this means is that the column named **idStatusDescription** of the catalog table named **tblStatusDescription** is referenced by the **idStatusDescription** column of the **tblStatusPerCustomer** table, and therefore, orphan **idStatusDescription** values probably don't exist in the **tblStatusPerCustomer**, or in other words, there is no need to left join tables **tblStatusPerCustomer** and **tblStatusDescription**. Second (**main**) problem is the statement defining the alias named **sc** groups the status records by both **StatusDescription** and **idCustomer**, thus returning multiple records per customer instead of desired one. Third problem is that after multiple records per customer have already been selected by the statement aliased as **sc**, any further grouping does not produce desired result. Finally, the last problem is the presence of the **distinct top 100 percent** does not really achieve anything as it is probably disregarded by the engine altogether. Additionally, if there is a customer without any status records then such customer will not be returned by the query at all because **sc** is inner joined with **tblCustomer**. This might not be a problem though, it all depends whether every customer record has at least one match in the **tblStatusPerCustomer** table. **End Edit** --> What you are doing wrong is just about everything. You have customer table where a single record per customer might have 0 or more records in the status per customer table, so you probably want to join the status per customer table with the grouped by customer id version of itself to get only one record per customer with the status corresponding to the latest record by last modification field. Here is the script which should work: select cl.Name, t.StatusDescription, t.DateLastmodification from tblCustomer cl left join ( select a.idCustomer, sd.StatusDescription, a.DateLastmodification from tblStatusPerCustomer a inner join ( select idCustomer, max(DateLastmodification) DateLastmodification from tblStatusPerCustomer group by idCustomer ) b on a.idCustomer = b.idCustomer and a.DateLastmodification = b.DateLastmodification inner join tblStatusDescription sd on a.idStatusDescription = sd.idStatusDescription ) t on cl.idCustomer = t.idCustomer; Additionally, you might want to consider changing the table and column names as they are very misleading. For example, DateLastmodification should probably be DateLastModification for consistency with other columns such as StatusDescription which is properly named. Otherwise, you will quickly run into problems in case sensitive database. Hope this helps, Oleg
@Oleg I wasn't really offended, I just wanted to clarify where I stand at the moment in these matters. Like I said: I am very happy with the outcome and the additional tips you gave me concerning the 2 extra records. I will try them tomorrow (20:40 hrs local time) and let you know the outcome. I looked for the 'Accepted answer' function earlier but I couldn't find it. I'll do it right after sending this message.
@Oleg Thank you very much for solving my problem. Concerning "What you are doing wrong is just about everything..." I can only say that I am new to such complex queries in SQL. So what I put together is based on combining examples from books and Internet. With your example I now only get 2 records more than when I would perform a simple: select idCustomer, max(DateLastmodification) DateLastmodification from tblStatusPerCustomer group by idCustomer But this could be due to some pollution in one of the tables tblCustomers or tblStatusPerCustomer. For now I am pleased with what I've got and will proceed from here. Berend