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.
| Author |
Topic |
|
DTFan
Yak Posting Veteran
52 Posts |
 Posted - 2005-09-08 : 09:14:31
|
OK. First off let me apologize for the length of this post. I wanted to explain what I'm trying to accomplish in my test program but don't know how to do it without a little verbage. I've got 7 tables that I'm going to be hitting. I've got the majority of the SQL statement written (correctly, I believe). But the last part I'm having some trouble with. Here are some shorts parts of the different tables I have:tblStudent studID (p/k int) studFName (v/c) studLName (v/c) studEmail (v/c) ...tblProfessor profID (p/k int) depID (f/k tinyint) profFName (v/c) profLName (v/c) ...tblDepartment depID (p/k tinyint) depName (v/c)tblClass classID (p/k int) depID (f/k tinyint) classNumber (v/c) className (v/c) classCredits (tinyint) ...tblStudentSchedule ssID (p/k int) studID (f/k int) classID (f/k int) profID (f/k int) depID (f/k tinyint) scStartDate (smalldatetime) scEndDate (smalldatetime) scClassTime (smalldatetime) ...tblStudentReview srID (p/k int) srTitle (v/c) srDesc (v/c)tblStudentClassReview scrID (p/k int) srID (f/k int) ssID (f/k int) scrDateIn (smalldatetime) scrDetails (v/c) ...What I am trying to do is get the students name, professor name, class name, department ID, department name, etc for ALL students who have already started class (based on comparing Now() to tblStudentSchedule.scEndDate). That part is working. Now, I want to include something else. If an entry for tblStudentClassReview has been entered (where there will be a matching ssID), then I want to show the tblStudentReview.srTitle name. If not (meaning no ssID in the tblStudentClassReview table), then I want to show something like "No Review Entered." I wasn't sure if I could do that through the actual SQL code, so I made srID #1: "No Review Entered". So, if it is possible then I'll just remove that first entry. I'm hoping that I can check for NULL (meaning no ssID in the tblStudentClassReview table) and if it's encountered, then make it say "No Review Entered."Here's the code I have thus far. Like I said, the first part is working except it's NOT showing the tblStudentReview.srTitle.SELECT tblStudentSchedule.ssID, tblStudentSchedule.studID, tblStudentSchedule.classID, tblStudentSchedule.profID, tblStudentSchedule.depID, tblStudent.studLName + ', ' + tblStudent.studFName AS studentFullName, tblClass.className, tblDepartment.depName tblProfessor.profLName + ', ' + tblProfessor.profFName AS professorFullName,FROM tblStudentSchedule INNER JOIN tblStudent ON (tblStudentSchedule.studID = tblStudent.studID) INNER JOIN tblClass ON (tblStudenSchedule.classID = tblClass.classID) INNER JOIN tblDepartment ON (tblStudentSchedule.depID = tblDepartment.depID) INNER JOIN tblProfessor ON (tblStudentSchedule.profID = tblProfessor.profID)WHERE tblStudentSchedule.studID > 1AND CONVERT(datetime,CONVERT(varchar,tblStudentSchedule.scEndDate,101)) < Now()AND tblStudentSchedule.ssID NOT IN (SELECT ssID FROM tblStudentClassReview)ORDER BY tblStudent.studLName, tblStudent.studFName, tblClass.className I believe this is the code that works. I've tried changing several things (using RIGHT OUTER JOINs, using OUTER JOINs, etc.). I've tried changing the date code's and the INNER JOINs,etc. So some of this might not be 100% accurate because I've edited it so much and like a complete moron I didn't back-up what was partially working before I started changing things. Sorry 'bout that. Anyway, the last part that did work showed only the students who weren't currently listed in the tblStudentClassReview. What I want to do is show all students and, if they're not listed, then show the "No Review Entered" (preferably handling all of that through the SQL statement itself so I can remove that default #1 listing from tblStudentReview).Any and all help would be greatly appreciated. And thank you in advance for any guidance and/or help you can give.Thanks.DTFanEver-hopeful programmer-in-training  |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-09 : 20:12:50
|
This is a quick stab at this without reading too closely.  SELECT tblStudentSchedule.ssID, tblStudentSchedule.studID, tblStudentSchedule.classID, tblStudentSchedule.profID, tblStudentSchedule.depID, tblStudent.studLName + ', ' + tblStudent.studFName AS studentFullName, tblClass.className, tblDepartment.depName, tblProfessor.profLName + ', ' + tblProfessor.profFName AS professorFullName, ISNULL(tblStudentReview.srTitle,'No Review Entered')FROM tblStudentSchedule INNER JOIN tblStudent ON (tblStudentSchedule.studID = tblStudent.studID) INNER JOIN tblClass ON (tblStudenSchedule.classID = tblClass.classID) INNER JOIN tblDepartment ON (tblStudentSchedule.depID = tblDepartment.depID) INNER JOIN tblProfessor ON (tblStudentSchedule.profID = tblProfessor.profID) LEFT OUTER JOIN tblStudentReview ON tblStudentReview.ssID = tblStudentSchedule.ssIDWHERE tblStudentSchedule.studID > 1AND CONVERT(datetime,CONVERT(varchar,tblStudentSchedule.scEndDate,101)) < getdate()AND tblStudentSchedule.ssID NOT IN (SELECT ssID FROM tblStudentClassReview)ORDER BY tblStudent.studLName, tblStudent.studFName, tblClass.classNameYou'll need to replace the GETDATE() with Now(). I'm assuming you're using Access for this???MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
DTFan
Yak Posting Veteran
52 Posts |
Posted - 2005-09-12 : 07:29:55
|
quote:
Originally posted by derrickleggett
This is a quick stab at this without reading too closely. You'll need to replace the GETDATE() with Now(). I'm assuming you're using Access for this???MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
derrick -Actually I'm using MSDE. I just got your response so I'm going to try it out but I wanted to respond about the database that I was using. I'll let you know if this works. For the record the SQL statement you wrote is, well, way beyond something I would have figured out (or been able to write).Keeping my fingers crossed (which, of course, makes typing rather difficult)DTFanEver-hopeful programmer-in-training |
|
|
|
DTFan
Yak Posting Veteran
52 Posts |
Posted - 2005-09-12 : 09:37:30
|
| It worked great. I had to tweak the database a little but nothing big. Thank you very much for the help on that (I can pretty much guarantee that I wouldn't have figured that one out on my own ... at least not for quite a long time).One additional question. What if I want to show one or two of the fields from the tblStudentClassReview table? I thought I could run an INNER JOIN on tblStudentReview and tblStudentClassReview based on SRID but each thing I tried kept throwing an "error near INNER JOIN".DTFanEver-hopeful programmer-in-training |
|
|
|
DTFan
Yak Posting Veteran
52 Posts |
Posted - 2005-09-12 : 10:18:10
|
| I got it. I just had to insert an additional LEFT OUTER JOIN after the first one and that worked using the ssID to tie the two tables together. :)Thanks again for all the help. Once again this site has saved me several dollars in Excedrin DTFanEver-hopeful programmer-in-training |
|
|
|
|
|
|
|
|
[RESOLVED] Inner Join in combination with Outer