Joining tables

[ryanbrand]

Hi,
I have a complex database issue. There are 3 tables that I have to join. Here are what the tables look like:

TABLE: MajorListings

MajorName MajorCode
----------------------
Pre-Med XBIO
History HIST
Theology THEO


TABLE: studentInfo

studentID StudentMajorCode
----------------------------
3215456 HIST
8681235 THEO
3421343 XBIO


TABLE: submittedStudents

studentID Results
--------------------
3215456 answer
8681235 message
3421343 problem


I need this information to be displayed as follows: the majorCodes that had students with the results not empty, should be displayed in alphabetical order. Next to the major the total number of answer (where result=answer) for that major should be displayed as well as the total message (result=message) and total problem (result=problem). It's pretty difficult for me to do since I haven't had much experience joining tables.

Thank you for your help.

Ryan

 

[r937]

[tt]select MajorName
, sum( case when Results='answer'
then 1 else 0 end ) as answers
, sum( case when Results='message'
then 1 else 0 end ) as messages
, sum( case when Results='problem'
then 1 else 0 end ) as problems
from MajorListings
inner
join studentInfo
on MajorListings.MajorCode
= studentInfo.StudentMajorCode
inner
join submittedStudents
on studentInfo.studentID
= submittedStudents.studentID
group
by MajorName
order
by MajorName [/tt]

rudy

http://r937.com/

SQL Consulting