Combining Fields from Multiple Tables

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)

Combining Fields from Multiple Tables

Author

Topic

ggardei
Starting Member

6 Posts

Posted - 2010-03-31 : 11:24:15

I have three tables, each with different number of fields and different field names.

[ Q1_Attendance ]
StudentID
Q1EA
Q1UA
Q1ET
Q1UT

[ Q2_Attendance ]
StudentID
Q2EA
Q2UA
Q2ET
Q2UT

[ Q3_Attendance ]
StudentID
Q3EA
Q3UA
Q3ET
Q3UT
Q3LE
Q3HD
Q3P

I need a query that returns all those fields for student StudentID from every table, even if student doesn't have a record in one or more of the tables (returned fields = NULL). For example; one of our kindergarten students was enrolled in quarter 3, so she has no records in the Q1 or Q2 tables but does in Q3. I haven't been able to figure this out.

Thanks for the help!

SELECT
SUM ( cast(ExAbsence AS float) ) AS Q1EA,
SUM ( cast(UnAbsence AS float) ) AS Q1UA,
SUM ( cast(ExTardy AS float) ) AS Q1ET,
SUM ( cast(unTardy AS float) ) AS Q1UT
INTO #Temp_Q1Data
FROM dbo.ACA_TempQ1AttendanceRecord
WHERE studentid = @StudentID
GROUP BY studentid

SELECT
SUM ( cast(ExAbsence AS float) ) AS Q2EA,
SUM ( cast(UnAbsence AS float) ) AS Q2UA,
SUM ( cast(ExTardy AS float) ) AS Q2ET,
SUM ( cast(unTardy AS float) ) AS Q2UT
INTO #Temp_Q2Data
FROM dbo.ACA_TempQ2AttendanceRecord
WHERE studentid = @StudentID
GROUP BY studentid

select
sum (Case status when 'P' then 1 else 0 end) as Q3P,
sum (Case status when 'EA' then 1 else 0 end) as Q3EA,
sum (Case status when 'UA' then 1 else 0 end) as Q3UA,
sum (Case status when 'ET' then 1 else 0 end) as Q3ET,
sum (Case status when 'UT' then 1 else 0 end) as Q3UT,
sum (Case status when 'LE' then 1 else 0 end) as Q3LE,
sum (Case status when 'HD' then 1 else 0 end) as Q3HD
into #Temp_Q3Data
from ACA_ELMAttendanceRecord
WHERE date >= '1/1/2010' AND date <= '3/12/2010' -- this needs to be replaced with quarterdates lookup
AND studentid = @StudentID
GROUP BY studentid

SELECT @StudentID AS StudentID, Q1EA, Q1UA, Q1ET, Q1UT, Q2EA, Q2UA, Q2ET, Q2UT, Q3EA, Q3UA, Q3ET, Q3UT, Q3LE, Q3HD, Q3P
FROM #Temp_Q1Data, #Temp_Q2Data, #Temp_Q3Data

George W. Gardei
God's Bible School & College
(513) 763-6516
1810 Young Street
Cincinnati, OH. 45202

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 11:30:01

A full outer join or Union should work. For example,

SELECT @StudentID AS StudentID, Q1EA, Q1UA, Q1ET, Q1UT, Q2EA, Q2UA, Q2ET, Q2UT, Q3EA, Q3UA, Q3ET, Q3UT, Q3LE, Q3HD, Q3P
FROM #Temp_Q1Data q1 full join #Temp_Q2Data q2 on q1.StudentID = q2.StudentID
full join #Temp_Q3Data q3 on q2. StudentID = q3.StudentID

Harsh Athalye
http://www.letsgeek.net/

ggardei
Starting Member

6 Posts

Posted - 2010-03-31 : 11:47:38

Thanks, I knew it was something simple I was overlooking...

George W. Gardei
God's Bible School & College
(513) 763-6516
1810 Young Street
Cincinnati, OH. 45202

ggardei
Starting Member

6 Posts

Posted - 2010-03-31 : 11:57:31

and the results are...

StudentID Q1EA Q1UA Q1ET Q1UT Q2EA Q2UA Q2ET Q2UT Q3EA Q3UA Q3ET Q3UT Q3LE Q3HD Q3P
A0000002717 NULL NULL NULL NULL NULL NULL NULL NULL 2 1 0 1 0 0 38

which is what I wanted. :-)

George W. Gardei
God's Bible School & College
(513) 763-6516
1810 Young Street
Cincinnati, OH. 45202

PackRat
Starting Member

26 Posts

Posted - 2010-03-31 : 12:00:52

two untested examples for you;

1: using the original source in sub queries, if you're aggregating a single student you don't need the GROUP BY clause; since each query will return at most one row you don't have to worry about cartesian products and the following ought to be fine.


SELECT @StudentID AS StudentID
	, Q1EA, Q1UA, Q1ET, Q1UT
	, Q2EA, Q2UA, Q2ET, Q2UT
	, Q3EA, Q3UA, Q3ET, Q3UT, Q3LE, Q3HD, Q3P 
FROM (
		SELECT 
			SUM ( cast(ExAbsence AS float) ) AS Q1EA,
			SUM ( cast(UnAbsence AS float) ) AS Q1UA,
			SUM ( cast(ExTardy AS float) ) AS Q1ET,
			SUM ( cast(unTardy AS float) ) AS Q1UT
		FROM dbo.ACA_TempQ1AttendanceRecord 
		WHERE studentid = @StudentID
	) [Q1]
	FULL JOIN (
		SELECT 
			SUM ( cast(ExAbsence AS float) ) AS Q2EA,
			SUM ( cast(UnAbsence AS float) ) AS Q2UA,
			SUM ( cast(ExTardy AS float) ) AS Q2ET,
			SUM ( cast(unTardy AS float) ) AS Q2UT
		FROM dbo.ACA_TempQ2AttendanceRecord 
		WHERE studentid = @StudentID
	) [Q2] ON 1=1
	FULL JOIN (
		select 
			sum (Case status when 'P' then 1 else 0 end) as Q3P,
			sum (Case status when 'EA' then 1 else 0 end) as Q3EA, 
			sum (Case status when 'UA' then 1 else 0 end) as Q3UA, 
			sum (Case status when 'ET' then 1 else 0 end) as Q3ET, 
			sum (Case status when 'UT' then 1 else 0 end) as Q3UT, 
			sum (Case status when 'LE' then 1 else 0 end) as Q3LE,
			sum (Case status when 'HD' then 1 else 0 end) as Q3HD
		from ACA_ELMAttendanceRecord
		WHERE studentid = @StudentID AND date BETWEEN '1/1/2010' AND '3/12/2010' -- this needs to be replaced with quarterdates lookup
	) [Q3] ON 1=1

2: if you wanted to aggregate multiple students at once;


SELECT 
	 StudentID=[S].StudentID

	,Q1EA=SUM ( cast([Q1].ExAbsence AS float) )
	,Q1UA=SUM ( cast([Q1].UnAbsence AS float) )
	,Q1ET=SUM ( cast([Q1].ExTardy AS float) )
	,Q1UT=SUM ( cast([Q1].unTardy AS float) )

	,Q2EA=SUM ( cast([Q2].ExAbsence AS float) )
	,Q2UA=SUM ( cast([Q2].UnAbsence AS float) )
	,Q2ET=SUM ( cast([Q2].ExTardy AS float) )
	,Q2UT=SUM ( cast([Q2].unTardy AS float) )

	,Q3P =sum (Case [Q3].[status] when 'P' then 1 else 0 end)
	,Q3EA=sum (Case [Q3].[status] when 'EA' then 1 else 0 end)
	,Q3UA=sum (Case [Q3].[status] when 'UA' then 1 else 0 end)
	,Q3ET=sum (Case [Q3].[status] when 'ET' then 1 else 0 end)
	,Q3UT=sum (Case [Q3].[status] when 'UT' then 1 else 0 end)
	,Q3LE=sum (Case [Q3].[status] when 'LE' then 1 else 0 end)
	,Q3HD=sum (Case [Q3].[status] when 'HD' then 1 else 0 end)

FROM (SELECT @StudentID StudentID) [S] 
     /*could substitute the single student with the students table to aggregate all students*/
	LEFT JOIN dbo.ACA_TempQ1AttendanceRecord [Q1]
		ON [S].[StudentID]=[Q1].[StudentID]
	LEFT JOIN dbo.ACA_TempQ2AttendanceRecord [Q2]
		ON [S].[StudentID]=[Q2].[StudentID]
	LEFT JOIN ACA_ELMAttendanceRecord [Q3] 
		ON [S].[StudentID]=[Q3].[StudentID] AND [Q3].[date] BETWEEN '1/1/2010' AND '3/12/2010'
GROUP BY [S].StudentID

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources