asked
Maxvalue from 3 tables
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?
Is there really a status for a customer (StatusPerCustomer) that doesn't have a matching status? I think that your left joins are likely causing you some issues.
<\!-- **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
>What you are doing wrong is just about everything Love it!
@Matt Whitfield Well, there are few things wrong with the original query, and I know that other guys are pretty fast on their fingers too, so if I would delve into detailed splaining about why the query is wrong, I would definitely miss the boat. I am actually curious about what the engine is thinking about the query? There is an unthinkable **distinct top 100 percent** on the top which means that it must restate it into a suitable **group by**, but the **group by** is already present on the bottom. The group by does not really mean anything because there is not anything to group, so the question is what does the engine do? I suspect that it simply discards the **distinct top 100 percent** part, but this is just my guess.
@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
@Berend Storm I did not try to offend you by saying this. Believe me, I had my time of frustration in the past when desperately trying to get the records I need by randomly adding distinct to the top, changing inner joins to left or right etc. All I tried to say was since the query had few problems, it would be difficult for me to dissect it in the timely manner, that is all, and my attempt did not come out right, so I apologize. On the unrelated note, if my answer helped you, please mark it as accepted by ticking the box next to my answer. The question can be marked as accepted only by the person who asked it. Having accepted answers makes other site users to find the answers to their own questions faster (accepted answers are moved to the top for all questions with multiple answers; if there is no accepted answer then the answers are sorted by upvotes desc; any question can have only one accepted answer).
@Berend Storm Concerning the 2 extra records you get, they are caused by the fact that you have multiple records for the same customer in the **tblStatusPerCustomer** table and all records have exact same DateLastmodification value which also happens to be the max. This is a small problem and can be easily cured, but the solution depends on the values in **idStatusDescription** column for those records in question. If the values are the same (the last status record happens to be a simple duplicate) then you can get rid of them by either adding a line reading **group by a.idCustomer, sd.StatusDescription, a.DateLastmodification** after the line reading **on a.idStatusDescription = sd.idStatusDescription** in the subselect or adding the word distinct before the line reading **a.idCustomer, sd.StatusDescription, a.DateLastmodification** Either of the above resolves to the same statement by the engine, and will work for you. However, if the status records for the same customer same **DateLastmodification** which is also a max have different values in the idStatusDescription then the question is which one you would like to pick, and the solution will be based on this decision.
@Berend, everyone has sto start at the beginning. To paraphrase, "There is no royal road to SQL Mastery."
@Matt Whitfield Matt, we all have to start somewhere. Therefore I am glad with any help. So yes, I love it too ;>))
@Berend Storm - Please don't be discouraged! Obviously something that a beginner will find frustrating is actually pretty funny to someone who has lots of experience. Oleg is an intensely knowledgeable guy - you're in good hands there... :)
@Matt Whitfield I have noticed that Oleg knows what he is talking about. He also knows how to explain these things to others which is not a capacity everyone possesses. I am glad that I signed up for this forum.
@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.
@Berend Storm There is one more thing which the moderators of this site try to prevent from happening, but it happens frequently: people who ask questions opt to add an answer to their own question rather than adding a comment to existing answer. The former produces answers which are really meant to be comments while the latter keeps the site tidy as it reduces the number of threads, so the latter is more desirable. We might have a problem with visibility of the **add comment** and **accept answer** features though, but I am not sure how it works. Maybe there is a problem with visibility of these features due to the total number of karma points, I am not sure (when the user has too few points, the features are not visible).
@Oleg Thank your for informing me. As you can see I immediately followed your advise. In the meantime I got my (sub)query to work exactly as expected. For now this tread is finished for me. Thanks again.
