Join to subquery - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Join to subquery

Author

Topic

tig2810
Starting Member

9 Posts

Posted - 2010-02-26 : 19:35:37

Hi
I need to join 2 tables but the key i'm using to join the tables is duplicated 3 times in one of the tables so I get a row inflation problem. I was therefore thinking i need to join to a subquery but i just cant work it out and was hoping for some assistance.

SELECT
Department,
AccountCode,
InvoiceNumber,
InvoiceDate,
Amount,
Journal
FROM Ledger
WHERE AccountCode LIKE 'RC%' AND (AllocationMkr <> 'P')
ORDER BY Amount

-- but I also want to show the credit limit and payment terms

-- chart of accounts - below filters entries so that only 1 record ---- per account is returned
(SELECT DISTINCT AccountCode, CreditLimit, PaymentTerms
FROM ChartOfAccounts
WHERE (DB_Code = 'CBX') and AccountCode like 'RC%')

Could someone help my get this to work please?
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 23:28:20

[code]SELECT
l.Department,
l.AccountCode,
l.InvoiceNumber,
l.InvoiceDate,
l.Amount,
l.Journal
FROM Ledger l
JOIN (SELECT DISTINCT AccountCode, CreditLimit, PaymentTerms
FROM ChartOfAccounts
WHERE (DB_Code = 'CBX') and AccountCode like 'RC%') c
ON c.AccountCode = l.AccountCode
WHERE l.AccountCode LIKE 'RC%' AND (l.AllocationMkr <> 'P')
ORDER BY l.Amount
[/code]

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