Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL: I want to join to a 2nd multirecord table

Author  Topic 

pvdochtend
Starting Member

2 Posts

Posted - 2012-12-21 : 07:28:35
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

ADDED: 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)...

ClientNr DebtorNr ActivityDate ActivityTime Administrator ActivityType Description AgendaDate AgendaAction AgendaDescription Subject Type IdLog
1 100 2008-05-07 16:20:57.020 2008-05-07 16:20:57.020 MK Dispuut 7100998 2008-05-07 16:20:57.020 Activity 15480
1 100 2008-05-07 16:20:57.067 2008-05-07 16:20:57.067 MK Agenda 2008-06-06 00:00:00.000 Dispuut Dispuut opvolging Activity 15481
1 146 2008-05-07 16:50:35.563 2008-05-07 16:50:35.563 MK CF Correspondentie CF Rekeningoverzicht 2008-05-21 00:00:00.000 Opvolgen Agenda Activity 15484
1 109 2008-05-20 14:31:37.127 2008-05-20 14:31:37.127 MK Incasso Incasso-overdracht NULL Activity 15517
1 152 2008-05-21 13:26:33.867 2008-05-21 13:26:33.867 MK Bellen 1ste Belactie 2008-05-27 00:00:00.000 CF Correspondentie CF Aanmaning Activity 15572
1 37 2008-05-21 13:26:47.790 2008-05-21 13:26:47.790 MK Bellen 1ste Belactie 2008-05-21 00:00:00.000 CF Correspondentie CF Ingebrekestelling Activity 15573

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-21 : 07:49:42
You can use the APPLY operator like shown below. Alternatively, you could use a subquery and join to that
.....       
CASE
WHEN Invoice.BlockUnblock = 1 THEN 'CLIENT'
WHEN Invoice.Dispute = 'Dispuut' THEN 'CLIENT'
ELSE 'CF'
END AS ActieLigtBij,
t2.[Description]

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
OUTER APPLY
(
SELECT TOP (1)
idLog,
Clientnr,
Debtornr,
ActivityDate,
ActivityTime,
DESCRIPTION
FROM
Table2 t2
WHERE
t2.Debtornr = Debtor.DebtorNr
AND t2.ClientNr = Debtor.ClientNr
ORDER BY
ActivityDate DESC,
idlog DESC
) t2


WHERE (
Client.ClientNr = 5267
OR Client.ClientNr = 5395
OR Client.ClientNr = 5292
)
.....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 07:51:11
post some sample data from each of tables and then explain what you want as output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pvdochtend
Starting Member

2 Posts

Posted - 2012-12-21 : 08:44:05
I added some information above.... I hope this explains it a little...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 08:56:26
[code]
SELECT *
FROM
(
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)
)p
INNER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY ClientNr,DebtorNr ORDER BY ActivityDate DESC) AS Seq,
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'
)q
ON q.Seq=1
AND q.ClientNr = p.Klantnr
AND q.DebtorNr = p.Debiteurnr
ORDER BY DebiteurNaam
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -