Full Outer 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

General SQL Server Forums

New to SQL Server Programming

Full Outer Join

Author

Topic

lcsgeek
Starting Member

38 Posts

Posted - 2013-02-10 : 10:34:44

This query (Fall Scores):


	SELECT
		studentID as fSid
		, TestRITScore as fScore
	FROM 
		tblTestScores
	WHERE 
		(TermName = 'Fall 2012') 
		AND (GradeName = '4') 
		AND (MeasurementScaleName = 'Mathematics')
		AND (RIGHT(TestTypeName,5) = 'Goals')
	ORDER BY
		studentID

Returns this data (Fall Scores):
fSid fScore
118 220
1201481 204
1201535 210
1201850 200
1599 208
164 215
1921 212
195 194
196 203
197 195
200 201
210 202
229 200
239 230
243 200
245 216
256 201
298 232
340 198
384 189
406 200
48 188
525 181
57 191
62 200
89 214

This query (Winter Scores):


	SELECT
		studentID as wSid
		, TestRITScore as wScore
	FROM 
		tblTestScores
	WHERE 
		(TermName = 'Winter 2013') 
		AND (GradeName = '4') 
		AND (MeasurementScaleName = 'Mathematics')
		AND (RIGHT(TestTypeName,5) = 'Goals')
	ORDER BY
		studentID

Returns this data (Winter Scores):
wSid wScore
118 224
1201481 203
1201535 207
1201850 213
1201933 217
1201963 191
1201964 194
1599 228
164 218
1921 223
196 209
197 205
200 209
210 211
229 213
239 231
243 199
245 210
256 204
298 232
340 200
384 189
48 203
525 189
57 199
62 202
89 214

This query (Combined Fall and Winter scores):


	SELECT
		tFall.studentId as fSid
		, tFall.TestRITScore as fScore
		, tWinter.wSid
		, tWinter.wScore
	FROM 
		tblTestScores tFall
		FULL OUTER JOIN 
		(SELECT
			studentId as wSid
			, TestRITScore as wScore
		FROM 
			tblTestScores
		WHERE 
			(TermName = 'Winter 2013') 
			AND (GradeName = '4') 
			AND (MeasurementScaleName = 'Mathematics')
			AND (RIGHT(TestTypeName,5) = 'Goals')
		) tWinter ON tFall.StudentID = tWinter.wSid
	WHERE 
		(TermName = 'Fall 2012') 
		AND (GradeName = '4') 
		AND (MeasurementScaleName = 'Mathematics')
		AND (RIGHT(TestTypeName,5) = 'Goals')
	ORDER BY
		tFall.StudentName

Returns this data (Combined Fall and Winter scores):
fSid fScore wSid wScore
1599 208 1599 228
48 188 48 203
57 191 57 199
62 200 62 202
1921 212 1921 223
89 214 89 214
1201850 200 1201850 213
118 220 118 224
164 215 164 218
195 194 NULL NULL
196 203 196 209
197 195 197 205
200 201 200 209
210 202 210 211
229 200 229 213
1201481 204 1201481 203
239 230 239 231
243 200 243 199
245 216 245 210
256 201 256 204
298 232 298 232
340 198 340 200
1201535 210 1201535 207
384 189 384 189
406 200 NULL NULL
525 181 525 189

lcsgeek
Starting Member

38 Posts

Posted - 2013-02-10 : 10:42:19

If you look at the Winter Scores result set you'll see a studentId of 1201963 with a score of 191 and studentId of 1201964 with a score of 194 which don't appear on the combined query. I want all fall and winter records to be combined in the combined query and it looks like its only returning NULLs on one side of the query. Can anyone provide direction?

lcsgeek
Starting Member

38 Posts

Posted - 2013-02-10 : 10:55:09

I know that the combined WHERE (TermName = 'Fall 2012') is causing my problem but I'm stumped on how to include the winter scores. Obviously WHERE (TermName = 'Fall 2012' OR TermName = 'Winter 2013') would return multiple records but I want them combined in a single record with NULLs on both sides.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-10 : 11:22:34

If there is only one score per student id in each of the queries (i.e, each Term), you can use an aggregate function (such as MAX) as shown below. If you have more than one score for a given student ID, then there has to be more information on how to present the results

	SELECT
		studentID as fSid
		, MAX(CASE WHEN TermName = 'Fall 2012' THEN TestRITScore END) AS fScore
		, MAX(CASE WHEN TermName = 'Winter 2013' THEN TestRITScore END) AS wScore		
	FROM 
		tblTestScores
	WHERE 
		(GradeName = '4') 
		AND (MeasurementScaleName = 'Mathematics')
		AND (RIGHT(TestTypeName,5) = 'Goals')
	GROUP BY
		studentID
	ORDER BY
		studentID

It lookslike studentID is always numeric, but you have the data type as varchar. If it is guaranteed that the student id will always be numeric, it would be better to make the datatype numeric as well.

lcsgeek
Starting Member

38 Posts

Posted - 2013-02-10 : 11:37:13

Hi James,
Thanks as always for your contributions. As it turns out in this case I have historical information in my database from terms and years gone past and your query, by removing the term from the where clause returns all historical 4th grade scores - with NULLs of course but there's no way to distinguish between current and historical 4th graders.

lcsgeek
Starting Member

38 Posts

Posted - 2013-02-10 : 11:49:06

This did it:


	SELECT
		tFall.fSid
		, tFall.fStu
		, tFall.fScore
		, tWinter.wSid
		, tWinter.wStu
		, tWinter.wScore
	FROM 
				(SELECT
			termName as fTerm
			, studentId as fSid
			, studentName as fStu
			, TestRITScore as fScore
		FROM 
			tblTestScores
		WHERE 
			(TermName = 'Fall 2012') 
			AND (GradeName = '4') 
			AND (MeasurementScaleName = 'Mathematics')
			AND (RIGHT(TestTypeName,5) = 'Goals')
		) tFall
		FULL OUTER JOIN 
		(SELECT
			termName as wTerm
			, studentId as wSid
			, studentName as wStu
			, TestRITScore as wScore
		FROM 
			tblTestScores
		WHERE 
			(TermName = 'Winter 2013') 
			AND (GradeName = '4') 
			AND (MeasurementScaleName = 'Mathematics')
			AND (RIGHT(TestTypeName,5) = 'Goals')
		) tWinter ON tFall.fSid = tWinter.wSid
	ORDER BY
		tFall.fStu

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-10 : 12:08:14

Glad you got it working!

The query I posted would not work correctly if you had other semesters also. But, you can add a where clause to the query I posted to include both semesters:

	WHERE 
		TermName in ('Fall 2012','Winter 2013')
		AND (GradeName = '4') 
		AND (MeasurementScaleName = 'Mathematics')
		AND (RIGHT(TestTypeName,5) = 'Goals')

But what you posted is good as well. The reason I am proposing mine is because that requires only one pass through the table or index.

lcsgeek
Starting Member

38 Posts

Posted - 2013-02-10 : 12:52:46

James,
I really appreiciate your insight. I don't know what motivates you to get your head around someone elses trouble but I for one really appreicate it. Your solutions are clean and concise as well. Thanks again.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-10 : 17:13:09

Its no trouble lcs; I enjoy staring at SQL code. I enjoy everything about SQL and programming. Yeah, I know that sounds really geekish :)

On top of that, the company I am working for is going out of business, so the work load is very light. The major effort at this point is putting on a tie and suit and going to job interviews. So this is one way to keep myself busy.

Regardless, thank you for your kindness in saying it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources