Link to home
Start Free TrialLog in
Avatar of peterdidow
peterdidowFlag for Canada

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 = CompleteFinancialNeedAssessmentFlag, @CommunityFlag=CompleteCommunityVolunteerRatingFlag,
@AcademicFlag=CompleteAcademicAchievementRatingFlag  from awards where awardid=@AwardID
set @strSQL = 'select  b.ApplicationID,a.StudentID,a.Std_LastName,a.Std_MiddleName,a.Std_FirstName, a.GeneralComments, c.AwardName,c.AwardID,U.StudentSpecComment,
a.StudentEffectiveDate,b.AwardID,b.DateApplied, A.FinancialNeedRating, A.CommunityVolunteerRating, B.AcademicAchievementRating, B.ShortListedFlag, B.AdminComment,B.AwardTerm,B.ApplicationStartDate,B.ApplicationDeadLineDate  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.AcademicAchievementRating='''' or B.AcademicAchievementRating is null)'
if @CommunityFlag <> 0 and @AcademicFlag <> 0 set @strSQL = @strSQL + ' or '
if @CommunityFlag <> 0 set @strSQL = @strSQL + ' (A.CommunityVolunteerRating='''' 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.Std_LastName,a.Std_FirstName,a.StudentID'
--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
Avatar of samtran0331
samtran0331
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of peterdidow

ASKER

well it turns out there was a lookup table in between with just the studentid. I had to do a left join.....*=. Thanks though based on the info I gave noth comments were good.