USP_BBNC_CONSTITPRIMARYALUMNIDATA

            CREATE procedure dbo.USP_BBNC_CONSTITPRIMARYALUMNIDATA(@ID int)
            as

                set NOCOUNT on;

                select 
                    EDUCATIONALINSTITUTION.NAME,
                    EDUCATIONALHISTORYTYPECODE.DESCRIPTION as SCHOOLTYPE,
                    EDUCATIONALHISTORYSTATUSCODE.DESCRIPTION as STATUS,
                    EDUCATIONALDEGREECODE.DESCRIPTION as DEGREE,
                    EDUCATIONALHISTORY.STARTDATE,
                    EDUCATIONALHISTORY.ENDDATE,
                    --EDUCATIONALMAJORCODE.DESCRIPTION as MAJOR,

                    (select top 1 MAJORCODE.DESCRIPTION from dbo.EDUCATIONALMAJOR as MAJOR inner join dbo.EDUCATIONALMAJORCODE as MAJORCODE on MAJOR.EDUCATIONALMAJORCODEID = MAJORCODE.ID where MAJOR.EDUCATIONALHISTORYID = EDUCATIONALHISTORY.ID) as MAJOR,
                    (select top 1 MAJORCODE.DESCRIPTION from dbo.EDUCATIONALMINOR as MINOR inner join dbo.EDUCATIONALMAJORCODE as MAJORCODE on MINOR.EDUCATIONALMAJORCODEID = MAJORCODE.ID where MINOR.EDUCATIONALHISTORYID = EDUCATIONALHISTORY.ID) as MINOR,
                    EDUCATIONALHISTORY.GPA, 
                    EDUCATIONALHISTORY.CLASSOF,
                    EDUCATIONALHISTORY.CONSTITUENCYSTATUSCODE,
                    EDUCATIONALHISTORY.KNOWNNAME,
                    EDUCATIONALHISTORY.FRATERNITY
                from
                    dbo.CONSTITUENT
                inner join
                    dbo.EDUCATIONALHISTORY on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
                inner join 
                    dbo.EDUCATIONALINSTITUTION on EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
                left join
                    dbo.EDUCATIONALHISTORYTYPECODE on EDUCATIONALHISTORY.EDUCATIONALHISTORYTYPECODEID = EDUCATIONALHISTORYTYPECODE.ID
                left join
                    dbo.EDUCATIONALHISTORYSTATUSCODE on EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSCODEID = EDUCATIONALHISTORYSTATUSCODE.ID
                left join
                    dbo.EDUCATIONALDEGREECODE on EDUCATIONALHISTORY.EDUCATIONALDEGREECODEID = EDUCATIONALDEGREECODE.ID
                --left join

                    --dbo.EDUCATIONALMAJORCODE on EDUCATIONALHISTORY.EDUCATIONALMAJORCODEID = EDUCATIONALMAJORCODE.ID

                where 
                    CONSTITUENT.SEQUENCEID = @ID
                    and ISPRIMARYRECORD = 1;