Right outer join problem - 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 2008 Forums

Transact-SQL (2008)

Right outer join problem

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-04-20 : 10:06:34

I have a result set that comes from:

SELECT FieldA, SUM(FieldB) As MySum
FROM TableA WHERE MyCriteria = 'OnlyThese' 

GROUP BY MyCriteria, FieldA
ORDER BY MyCriteria, FieldA

The result is a couple of rows. I, however, want to have a certain subset from MyTable:


SELECT DISTINCT FieldB
FROM TableB
WHERE FieldC = 'MyAnotherValue'

TableA.FieldA matches to TableB.FieldB

so I want the whole


SELECT DISTINCT FieldB
FROM TableB
WHERE FieldC = 'MyAnotherValue'

as the result, with the missing MySums (i.e. nothing to sum) being 0. Isn't the solution RIGHT OUTER JOIN?

Could someone here help me with that?

Thanks in advance!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-20 : 10:30:44

I think you can just (left) outer join the two queries:

SELECT 
	*
FROM
	(
		SELECT DISTINCT FieldB
		FROM TableB
		WHERE FieldC = 'MyAnotherValue'
	) AS A
LEFT OUTER JOIN
	(
		SELECT FieldA, SUM(FieldB) As MySum
		FROM TableA WHERE MyCriteria = 'OnlyThese' 
		GROUP BY MyCriteria, FieldA
		ORDER BY MyCriteria, FieldA
	) AS B
	ON A.FieldB = B.FieldA

Note: It much easier for us to help you in you supply DDL, DML and expected output. These links will help you with that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-04-23 : 01:25:46

Thanks, it works!

I'll go thru those links too to see if there's something that makes it more clear, at least result set would surely help. It's just that I already try to abstract as much as possible to avoid questions like "what is X supposed to do?"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources