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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL: I want to join to a 2nd multirecord table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pvdochtend
Starting Member

2 Posts

Posted - 12/21/2012 :  07:28:35  Show Profile  Reply with Quote
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

Edited by - pvdochtend on 12/21/2012 08:43:28

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/21/2012 :  07:49:42  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 12/21/2012 :  07:51:11  Show Profile  Reply with Quote
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 - 12/21/2012 :  08:44:05  Show Profile  Reply with Quote
I added some information above.... I hope this explains it a little...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/21/2012 :  08:56:26  Show Profile  Reply with Quote

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


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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000