CODEWITHSUNDEEP
sqlsql-servert-sql
erdys
erdys

Reputation: 39

How to Join with select top

I have a problem with the combination of multiple tables. My SQL query:

SELECT *
FROM CRM.Bank as a
JOIN CRM.Documents as b ON a.Bank_ID = b.Documents_ID
JOIN CRM.Counterparties as c ON c.Counterparties_ID = b.Documents_ID
JOIN CRM.Items as d ON d.Document_tran_ID = b.Documents_ID

I have table CRM.Items, which has the following columns:

Item_ID     Document_tran_ID    Name
=======     ================    ====
1           1                   Advertising banner
2           1                   Shipping costs
3           2                   Garden tent
4           2                   Additional fasteners
5           2                   Shipping costs

And now I have a problem how to connect only the first items to the document (d.Document_tran_ID = b.Documents_ID ) ?? I know I should use SELECT TOP. However, I have a problem with creating the correct query

Expect the result in the form of:

Bank_ID     Documents_ID        Counterparties_ID       Document_tran_ID    Name
=======     ============        =================       ================    ====
22          1                   4                       1                   Advertising banner
23          2                   20                      2                   Garden tent
24          3                   21                      3                   Other

Only the first Item from the document is matched.

Upvotes: 1

Views: 109

Answers (2)

Jacek Wróbel
Jacek Wróbel

Reputation: 1222

I think you can try to use CROSS APPLY join. In the inner query, you can apply order condition for selecting TOP row

SELECT *
FROM CRM.Bank as a
JOIN CRM.Documents as b ON a.Bank_ID = b.Documents_ID
JOIN CRM.Counterparties as c ON c.Counterparties_ID = b.Documents_ID
CROSS APPLY
  (select top 1 * from CRM.Items i where  i.Document_tran_ID = b.Documents_ID) as d

Upvotes: 4

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use sub-query for documents table

SELECT *
FROM CRM.Bank as a
JOIN ( select min(Document_tran_ID) as Documents_ID from CRM.Documents) as b ON a.Bank_ID = b.Documents_ID
JOIN CRM.Counterparties as c ON c.Counterparties_ID = b.Documents_ID
JOIN CRM.Items as d ON d.Document_tran_ID = b.Documents_ID

Upvotes: 0

Related Questions