peterdidow
asked on
Help with join in stored Procedure
I am working with a fairly complex stored procedure. I need to pull StudentSpecComment from another table called User_StudentComment here is how it currently reads....
CREATE PROCEDURE StudentFinancialRating
(
@AcademicYear as Int,
@AwardID as Int,
@EndDateStart As datetime,
@EndDateEnd as datetime
--@AcademicFlag as Int,
--@CommunityFlag as Int,
--@FinancialFlag as Int
)
As
Declare @strSQL as Nvarchar(1000)
Declare @AcademicFlag as Int
Declare @CommunityFlag as Int
Declare @FinancialFlag as Int
Select @FinancialFlag = CompleteFinancialNeedAsses smentFlag, @CommunityFlag=CompleteCom munityVolu nteerRatin gFlag,
@AcademicFlag=CompleteAcad emicAchiev ementRatin gFlag from awards where awardid=@AwardID
set @strSQL = 'select b.ApplicationID,a.StudentI D,a.Std_La stName,a.S td_MiddleN ame,a.Std_ FirstName, a.GeneralComments, c.AwardName,c.AwardID,U.St udentSpecC omment,
a.StudentEffectiveDate,b.A wardID,b.D ateApplied , A.FinancialNeedRating, A.CommunityVolunteerRating , B.AcademicAchievementRatin g, B.ShortListedFlag, B.AdminComment,B.AwardTerm ,B.Applica tionStartD ate,B.Appl icationDea dLineDate from StudentInformation A, Applications B, Awards C, AwardDetails D,User_StudentComment u
where (B.DateApplied between ' + ''''+ cast(@EndDateStart as Varchar) +'''' + ' and ' + '''' + cast(@EndDateEnd as Varchar) + '''' + ')
and c.awardid =d.awardid and d.AcademicYear='+ ''''+ cast(@AcademicYear as Varchar) +'''' + ' and c.AwardID =' + ''''+ cast(@AwardID as Varchar) +'''' + ' and b.StudentID = a.StudentID and
b.DateApplied = a.StudentEffectiveDate and b.AwardID = C.AwardID '
if @AcademicFlag <> 0 or @CommunityFlag <> 0 or @FinancialFlag <> 0 set @strSQL = @strSQL + ' and ('
if @AcademicFlag <> 0 set @strSQL = @strSQL + ' (B.AcademicAchievementRati ng='''' or B.AcademicAchievementRatin g is null)'
if @CommunityFlag <> 0 and @AcademicFlag <> 0 set @strSQL = @strSQL + ' or '
if @CommunityFlag <> 0 set @strSQL = @strSQL + ' (A.CommunityVolunteerRatin g='''' or A.CommunityVolunteerRating is null)'
if (@AcademicFlag <> 0 or @CommunityFlag <> 0) and @FinancialFlag <> 0 set @strSQL = @strSQL + ' or '
if @FinancialFlag <> 0 set @strSQL = @strSQL + ' (A.FinancialNeedRating=''' ' or A.FinancialNeedRating is null)'
if @AcademicFlag <> 0 or @CommunityFlag <> 0 or @FinancialFlag <> 0 set @strSQL = @strSQL + ')'
set @strSQL = @strSQL + ' order by c.AwardName,c.AwardID,a.St d_LastName ,a.Std_Fir stName,a.S tudentID'
--print @strSQL
exec SP_ExecuteSQL @strSQL
GO
My table structure:
User_StudentCOmment
UserID pk
StudentID pk
StudentSpecCOmment
This could be joined with the StudentID as I have that in several tables currently. The problem is I am not quite sure how to join properly in this case. Here is some sample data:
23732 xxxIDNOxx Irvine Scott Smith NULL xxx Award 17544 2006-01-23 00:00:00.000 17544 2006-01-23 00:00:00.000 3 NULL 0 0 56.10 - BUS no essay Winter 2006 2005-12-12 00:00:00.000 2006-02-03 00:00:00.000
23732 xxxIDNOxx Irvine Scott Smith NULL xxx Memorial Award 17544 2006-01-23 00:00:00.000 17544 2006-01-23 00:00:00.000 3 NULL 0 0 56.10 - BUS no essay Winter 2006 2005-12-12 00:00:00.000 2006-02-03 00:00:00.000
anyways the point is it repeats and I only need the one row which has a value in the comment field ( from user_studentSpecComment)
CREATE PROCEDURE StudentFinancialRating
(
@AcademicYear as Int,
@AwardID as Int,
@EndDateStart As datetime,
@EndDateEnd as datetime
--@AcademicFlag as Int,
--@CommunityFlag as Int,
--@FinancialFlag as Int
)
As
Declare @strSQL as Nvarchar(1000)
Declare @AcademicFlag as Int
Declare @CommunityFlag as Int
Declare @FinancialFlag as Int
Select @FinancialFlag = CompleteFinancialNeedAsses
@AcademicFlag=CompleteAcad
set @strSQL = 'select b.ApplicationID,a.StudentI
a.StudentEffectiveDate,b.A
where (B.DateApplied between ' + ''''+ cast(@EndDateStart as Varchar) +'''' + ' and ' + '''' + cast(@EndDateEnd as Varchar) + '''' + ')
and c.awardid =d.awardid and d.AcademicYear='+ ''''+ cast(@AcademicYear as Varchar) +'''' + ' and c.AwardID =' + ''''+ cast(@AwardID as Varchar) +'''' + ' and b.StudentID = a.StudentID and
b.DateApplied = a.StudentEffectiveDate and b.AwardID = C.AwardID '
if @AcademicFlag <> 0 or @CommunityFlag <> 0 or @FinancialFlag <> 0 set @strSQL = @strSQL + ' and ('
if @AcademicFlag <> 0 set @strSQL = @strSQL + ' (B.AcademicAchievementRati
if @CommunityFlag <> 0 and @AcademicFlag <> 0 set @strSQL = @strSQL + ' or '
if @CommunityFlag <> 0 set @strSQL = @strSQL + ' (A.CommunityVolunteerRatin
if (@AcademicFlag <> 0 or @CommunityFlag <> 0) and @FinancialFlag <> 0 set @strSQL = @strSQL + ' or '
if @FinancialFlag <> 0 set @strSQL = @strSQL + ' (A.FinancialNeedRating='''
if @AcademicFlag <> 0 or @CommunityFlag <> 0 or @FinancialFlag <> 0 set @strSQL = @strSQL + ')'
set @strSQL = @strSQL + ' order by c.AwardName,c.AwardID,a.St
--print @strSQL
exec SP_ExecuteSQL @strSQL
GO
My table structure:
User_StudentCOmment
UserID pk
StudentID pk
StudentSpecCOmment
This could be joined with the StudentID as I have that in several tables currently. The problem is I am not quite sure how to join properly in this case. Here is some sample data:
23732 xxxIDNOxx Irvine Scott Smith NULL xxx Award 17544 2006-01-23 00:00:00.000 17544 2006-01-23 00:00:00.000 3 NULL 0 0 56.10 - BUS no essay Winter 2006 2005-12-12 00:00:00.000 2006-02-03 00:00:00.000
23732 xxxIDNOxx Irvine Scott Smith NULL xxx Memorial Award 17544 2006-01-23 00:00:00.000 17544 2006-01-23 00:00:00.000 3 NULL 0 0 56.10 - BUS no essay Winter 2006 2005-12-12 00:00:00.000 2006-02-03 00:00:00.000
anyways the point is it repeats and I only need the one row which has a value in the comment field ( from user_studentSpecComment)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER