Question

Locked

I want to join to a secondary table, i want to get only the latest record

By pvdochtend ·
SQL: I want to join a secondary query and i want to get only the latest record (there is a date field in that table.

Query 1 (Consists all the invoices of our customers and their debtors):
Clientnr
Debtornr
Invoicenr (unique)
Amount
..
..

Query 2 (Consists of all the log records, but for each debtor/client combination there can be a lot of records, i need the latest (latest date of highest idLog):
idLog
Clientnr
Debtornr
ActivityDate
ActivityTime
Description (this is the field i need)

In both of the tables i use several WHERE statements to filer the results...

I can't get it to work. I always get duplicated records for each table....

The big problem is that i can only join the tables using clientnr and debtornr. Not on an unique id... and i can't change the tables because it is from software we don't own.

I hope someone has an answer...

Thanks

Peter

ADDE Query where i need to add another table with multiple records, as explained above...:
--------------------------------------------------------------------------------------------------------
SELECT Client.ClientNr AS Klantnr, Client.ClientName AS Klantnaam, Debtor.DebtorNr AS Debiteurnr, Debtor.DebtorName AS DebiteurNaam,
Invoice.InvoiceNr AS Factuurnr, Invoice.InvoiceDate AS FactuurDatum, Invoice.VervalDatum AS Vervaldatum, Invoice.OpenstaandBedrag,
Invoice.DaysOpen AS DagenVervallen, CASE WHEN Invoice.BlockUnblock = 1 THEN 'JA' ELSE 'NEE' END AS GeblokkeerdJN,
Invoice.InvoiceBlocked AS BlokkeerOmschrijving, CASE WHEN Invoice.Dispute = 'Dispuut' THEN 'JA' ELSE 'NEE' END AS DispuutJN,
CASE WHEN Invoice.Dispute = 'Dispuut' THEN Dispuuttekst.TextFree ELSE 'nvt' END AS DispuutOmschrijving,
CASE WHEN Invoice.BlockUnblock = 1 THEN 'CLIENT' WHEN Invoice.Dispute = 'Dispuut' THEN 'CLIENT' ELSE 'CF' END AS ActieLigtBij
FROM Debtor INNER JOIN
Client ON Debtor.ClientNr = Client.ClientNr INNER JOIN
Invoice ON Debtor.ClientNr = Invoice.ClientNr AND Debtor.DebtorNr = Invoice.DebtorNr LEFT OUTER JOIN
(SELECT ClientNr, DebtorNr, TextFree
FROM Dispute) AS Dispuuttekst ON Debtor.ClientNr = Dispuuttekst.ClientNr AND Debtor.DebtorNr = Dispuuttekst.DebtorNr
WHERE (Client.ClientNr = 5267 OR
Client.ClientNr = 5395 OR
Client.ClientNr = 5292) AND (Invoice.OpenstaandBedrag <> 0) AND (Invoice.DaysOpen > 0) AND (Invoice.DaysOpen > 30)
ORDER BY DebiteurNaam
----------------------------------------------------------------------------------------------------
QUERY 2 (With multiple records):

SELECT
ClientNr,
DebtorNr,
ActivityDate,
ActivityTime,
Administrator,
ActivityType,
Description,
AgendaDate,
AgendaAction,
AgendaDescription,
Subject,
Type,
IdLog
FROM
LogFile
WHERE
Service = 'OUT'
AND type = 'Activity'
AND ActivityType <> ''
AND Activitytype <> 'specdeb'
=================================================================================================================

ADDED (2):
Results from the seperate queries:
-----------------------------------------------------------------------------------------------------------------
Results Query1 (copy paste into different text editor to see the right format):
-----------------------------------------------------------------------------------------------------------------
Clientnr ClientName DebtorNr DebtorName InvoiceNr InvoiceDate ExpireDate Value AmountDue Blocked BlockedComment Dispute DisputeComment Action
5267 TPF 95348 Abv 1123017 2012-07-26 00:00:00.000 2012-08-25 00:00:00.000 3442,38 118 J Niet manen NEE nvt CLIENT
5267 TPF 102797 Avof 1124214 2012-10-11 00:00:00.000 2012-11-10 00:00:00.000 531,26 41 J Niet manen NEE nvt CLIENT
5267 TPF 99098 BB 20112339 2011-07-28 00:00:00.000 2011-09-11 00:00:00.000 327,6 467 N NEE nvt CF
5267 TPF 109559 BTO 1121891 2012-05-16 00:00:00.000 2012-05-30 00:00:00.000 256 205 N NEE nvt CF
5267 TPF 119814 BPM 4120449 2012-09-13 00:00:00.000 2012-10-13 00:00:00.000 -286 69 N NEE nvt CF
5395 APB 108808 CFK 20121900 2012-10-04 00:00:00.000 2012-11-03 00:00:00.000 133,83 48 N NEE nvt CF
5395 APB 108808 CFK 20121901 2012-10-04 00:00:00.000 2012-11-03 00:00:00.000 333,44 48 N NEE nvt CF
5395 APB 108808 CFK 20121902 2012-10-04 00:00:00.000 2012-11-03 00:00:00.000 333,44 48 N NEE nvt CF
5395 APB 108808 CFK 20121903 2012-10-04 00:00:00.000 2012-11-03 00:00:00.000 333,44 48 N NEE nvt CF
--------------------------------------------------------------------------------------------------------------

results Query2 (copy paste into different text editor to see the right format):
the first 2 records are from 1 client/debtor combination, so i only need the desciption of the latest record (activitydate)...

Hope someone can help me...

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

A hint

by john.a.wills In reply to I want to join to a secon ...

Here is something I wrote within a WHERE clause for a similar problem (I wanted the latest address supplied)
and spraddr_from_date = (select max(b.spraddr_from_date)
from spraddr b
where b.spraddr_pidm = pebempl_pidm
and b.spraddr_atyp_code = 'PR'
and b.spraddr_status_ind is null
and b.spraddr_from_date <= sysdate)
You, I think, need to do something a little more complicated (you need to nest yet further) with the date and IDLog. Succes!

Collapse -

Another Hint

by kevinthek In reply to I want to join to a secon ...

Have you considered GROUP BY for your second query?

Select Field1, Field2, Field3, max(ActivityDate) as 'AliasedDate'
From Table
GROUP BY Field1, Field2, Field3

Collapse -

Or

by NickWy In reply to I want to join to a secon ...

SELECT TOP 1 ......

ORDER BY activitydate DESC

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums