Need help with a JOIN - 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 2012 Forums

Transact-SQL (2012)

Need help with a JOIN

Author

Topic

mr_max
Starting Member

10 Posts

Posted - 2014-06-10 : 03:37:16

I don't know if this is possible, but I want to join these two SQL statements into one, to improve performance.

The first one retrieves details of Quotes from the Quotes Table (along with other required details)

SELECT Quotes.QuoteNumber, ... (lots of fields) FROM Quotes LEFT JOIN Sales_Companies ON Quotes.CustomerCode = Sales_Companies.CustomerCode WHERE SupersededRevision = 'False' AND QuoteDate >= '2014-01-01' ORDER BY QuoteDate, ID ASC

The second statement checks whether an incomplete (not done) followup is scheduled for this quote, in the Sales_Followups table:
(This is part of the VB.NET code in my application - rs is the recordset from the SELECT statement above, which is cycled through to fill a table.)

SELECT COUNT(*) AS FU_Count FROM Sales_Followups WHERE QuoteNumber = '" & rs("QuoteNumber").Value & "' AND FollowupComplete = 'False'

What I'd like to know is: can I utilise another JOIN statement to merge these two queries, or do they have to be run separately?

(Edit: add code tags)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-10 : 11:46:32

Show us some sample data for the output. I don't see how you could "join" them together.

Also, your code is vulnerable to SQL injection. Use parameterized queries ONLY. Do not concatenate strings together to form a query. Better yet, use stored procedures.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-10 : 14:18:26

Calculate all counts at once, like this...

SELECT		f.QuoteNumber,
		COUNT(*) AS FU_Count
FROM		dbo.Sales_Followups AS f
INNER JOIN	dbo.Quotes AS q ON q.QuoteNumber = f.QuoteNumber
			AND q.SupersededRevision = 'False'
			AND q.QuoteDate >= '20140101'
WHERE		f.FollowupComplete = 'False'
GROUP BY	f.QuoteNumber;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-10 : 14:21:03

Or do it at once, like this

SELECT		q.QuoteNumber,
		f.FU_Count,
		... (lots of fields)
FROM		dbo.Quotes AS q
LEFT JOIN	dbo.Sales_Companies AS sc ON sc.CustomerCode = q.CustomerCode
CROSS APPLY	(
			SELECT	COUNT(*) AS FU_Count
			FROM	dbo.Sales_Followups AS sf
			WHERE	sf.QuoteNumber = q.QuoteNumber
				AND sf.FollowupComplete = 'False'
		) AS f
WHERE		q.SupersededRevision = 'False'
		AND q.QuoteDate >= '2014-01-01'
ORDER BY	q.QuoteDate,
		q.ID;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

mr_max
Starting Member

10 Posts

Posted - 2014-06-10 : 16:58:46

Awesome...thanks guys - will give it a go and let you know.

Edit: sp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources