INNER JOIN and COUNT - 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

SQL Server 2005 Forums

Transact-SQL (2005)

INNER JOIN and COUNT

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-09-08 : 15:51:45

Ok, really need some input on this one.
It's driving me crazy!!!

Here is the proc:

SELECT DISTINCT a.ImportAlertID, a.ImportTypeID, b.ImportType, c.Name, 
inv.sumInvoices, inv.numInvoices, a.FundingRuleChecked,
	
(SELECT COUNT(*) 
	FROM invoicenotadvanced i
	INNER JOIN ImportAlertAgings iaa ON iaa.ImportAlertID = i.ImportAlertID	
	WHERE Closed = 0 
	AND iaa.ImportAlertID = i.ImportAlertID) AS openInvoices,

	 
a.CompleteStatus, '' AS sumCurrentInvoices

FROM ImportAlert a
INNER JOIN ImportType b ON a.ImportTypeID = b.ImportTypeID
INNER JOIN Client c ON a.ClientID = c.pk_id
INNER JOIN (SELECT ImportAlertID, COUNT(*) AS numInvoices, SUM(InvAmount) AS sumInvoices	 
	FROM dbo.ImportAging
	GROUP BY ImportAlertID) inv 
	ON a.ImportAlertID = inv.ImportAlertID
WHERE a.CompleteStatus = 0 AND a.ImportTypeID = @type
AND DATEDIFF(day, ProcessDate, GETDATE()) <= 
	(SELECT Days FROM ImportMaxDays WHERE ImportTypeID = @type)
ORDER BY c.Name

The problem is the count (in bold).
I have a table for a reference, ImportAlertAgings.
Problem is, it's counting them all and for each row returned,
the count is the same.

The count should be for each distinct importalertid in ImportAlertAgings and the other table.

Suggestions?

Thanks,

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-08 : 16:00:50

You will need an outer reference for the subquery!

(SELECT COUNT(*)
FROM invoicenotadvanced i
INNER JOIN ImportAlertAgings iaa ON iaa.ImportAlertID = i.ImportAlertID
WHERE Closed = 0
AND iaa.ImportAlertID = a.ImportAlertID) AS openInvoices,

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources