How do I select the latest record against another tables record for each customer in SQL?

Question

0.00/5 (No votes)

See more:

I have three Tables in SQL:

tblCustomers
    pkCustomer <-- linked to fkCustomer
    strCustomerName

tblCommunicationsLogs
    pkCommunicationLog
    fkCustomer

tblCommunicationLogSteps
    pkCommunicationLogStep
    fkCommunicationLog <-- linked to pkCommunicationLog
    strCommunicationLogStep

Each customer has a Communication Log which the Communication Steps are logged to.

I would like to create a view to display the Latest Step of the Log for each Customer.

But i just can't get my head around how to write the Query.

What I have tried:

I have tried googling for examples but i'm not really that clued up on SQL and just don't understand it enough.

I know i have attempted this in the past and got close but for the life of me now i cannot remember what i did to carry on with it.

I guess it's along the lines of SELECT MAX from tblCommunicationLogSteps WHERE pkCommunicationLog = fkCommunicationLog in tblCommunicationSteps, AND maybe GROUP BY Customer?? but i just don't know.

Posted 10-Apr-18 2:20am

BeginnerCoderPete

Updated 10-Apr-18 16:32pm

v8

Add a Solution

Comments

#realJSOP 10-Apr-18 8:29am

We need to see the columns for each table.

BeginnerCoderPete 10-Apr-18 8:47am

tblCommunicationsLogs
pkCommunicationLog
fkCustomer

tblCommunicationLogSteps
pkCommunicationLogStep
fkCommunicationLog <-- linked to pkCommunicationLog
strCommunicationLogStep

tblCustomers
pkCustomer <-- linked to fkCustomer
strCustomerName

#realJSOP 10-Apr-18 9:26am

Use the "Improve question" link to change your question.

How can you determine the latest log step record if there's no datetime column indicating when the step was inserted into the table?

BeginnerCoderPete 10-Apr-18 9:47am

I was thinking the highest pkCommunicationLogSteps linked to the log would get me the latest step on that log.

CHill60 10-Apr-18 9:53am

That fkCommunicationsLog in tblCustomers implies each customer can only have one tblCommunication log entry ... is that really true?

BeginnerCoderPete 10-Apr-18 9:58am

Your right that was my mistake that table doesn't have fkCommunicationsLog. My mistake.

[no name] 10-Apr-18 9:54am

I would suggest to use CROSS APPLY:
SQL Server CROSS APPLY and OUTER APPLY[^]

...but not sure wheter it does work over two joins :(

2 solutions

Solution 1

I think these ideas work... a bit "brute force" and note using sub-queries is not the most performant (but it's a starting point :-) )

To get the steps for the most recent communication log entry:

SQL

select pkCustomer, strCustomerName, pkCommunicationLog, pkCommunicationLogStep, strCommunicationLogStep
from #tblCustomers C
left join #tblCommunicationsLogs L ON L.fkCustomer = c.pkCustomer
left join #tblCommunicationLogSteps S on S.fkCommunicationLog = L.pkCommunicationLog
inner join (SELECT MAX(pkCommunicationLog) as m, fkCustomer FROM #tblCommunicationsLogs GROUP BY fkCustomer) as fltr ON fltr.m=L.pkCommunicationLog
ORDER BY pkCommunicationLog,pkCommunicationLogStep

To get just the most recent log step by customer

SQL

select pkCustomer, strCustomerName, pkCommunicationLog, pkCommunicationLogStep, strCommunicationLogStep
from #tblCustomers C
left join #tblCommunicationsLogs L ON L.fkCustomer = c.pkCustomer
left join #tblCommunicationLogSteps S on S.fkCommunicationLog = L.pkCommunicationLog
inner join (SELECT MAX(pkCommunicationLogStep) as m, fkCommunicationLog FROM #tblCommunicationLogSteps GROUP BY fkCommunicationLog) as fltr ON fltr.m=S.pkCommunicationLogStep AND fltr.fkCommunicationLog = S.fkCommunicationLog
ORDER BY pkCommunicationLog,pkCommunicationLogStep

Posted 10-Apr-18 4:19am

CHill60

Comments

[no name] 10-Apr-18 10:23am

A 5 so far. Any idea wheter CROSS APPLY would work for this and would mabye be some more performant?

CHill60 10-Apr-18 15:34pm

No idea. I've binned the data now unfortunately

BeginnerCoderPete 10-Apr-18 10:36am

The query is timing out but it's definatly a nudge in the right direction i think which i appreciate greatly. I have also put SELECT TOP (10) aswell but still times out which is odd.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2018-04-10T16:34:00

Not having SQL editor available at the moment so please excuse any typos. What about something like

SQL

SELECT *
FROM tblCustomers c,
     tblCommunicationsLog cl,
     tblCommunicationLogSteps cls
WHERE cl.fkCustomer = c.pkCustomer
AND   cls.fkCommunicationLog = cl.pkCommunicationLog
AND   cls.pkCommunicationLogStep =
                 (SELECT MAX(cls2.pkCommunicationLogStep)
                  FROM   tblCommunicationLogSteps cls2
                  WHERE cls2.fkCommunicationLog = cl.pkCommunicationLog)

Or slightly different variant

SQL

SELECT *
FROM tblCustomers c,
     tblCommunicationsLog cl,
     tblCommunicationLogSteps cls
WHERE cl.fkCustomer = c.pkCustomer
AND   cls.fkCommunicationLog = cl.pkCommunicationLog
AND   NOT EXISTS (SELECT 1
                  FROM   tblCommunicationLogSteps cls2
                  WHERE cls2.fkCommunicationLog = cl.pkCommunicationLog
                  AND   cls2.pkCommunicationLogStep > cls.pkCommunicationLogStep)

Just ensure that you have indexed the foreign key columns.