SQL: I want to join to a 2nd multirecord table

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

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/

pvdochtend
Starting Member

2 Posts

Posted - 2012-12-21 : 08:44:05

I added some information above.... I hope this explains it a little...

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources