
Sql problems
I have a page an asp page that is working fine but I wanted to change something. I have a bunch of information placed in the database that is categorized. Before I just brought back the category that I wanted and if they have previously selected one then the checkmark was placed in the checkbox. But I have decided to bring back all of the options that the individual has not selected before.<BR><BR>My SQL statement works fine but it brings back each record seven or eight time. Does anyone have any suggestions about what I can change my statement so it will only write it tot he screen once.<BR><BR>thanks iin advance<BR><BR>This is my SQL statement<BR><BR>stSql = "SELECT CourseNo AS Course, CourseInformation.CourseName AS Name, CourseInformation.CourseCredits AS Credits FROM CourseInformation, GraduationPlan WHERE CourseInformation.CourseNo <> GraduationPlan.SelectedCourses AND StudentID = '" & Session("id") & "' ORDER BY CourseNO"<BR>

RE: Sql problems
try using Distinct<BR><BR>SELECT Distinct CourseNo AS Course, CourseInformation.CourseName AS Name, ...

Thanks...never tried that one.(EOP)

Really though...
You should rewrite the query properly. With or without the DISTINCT, that query is going to have *horrible* performance issues as the number of rows in your CourseInformation table grows. <BR><BR>Try something that won't cause a crossjoin in the first place instead, like this:<BR><BR>[code language="TSQL"]<BR>SELECT CourseNo AS Course, <BR> CourseInformation.CourseName AS Name, <BR> CourseInformation.CourseCredits AS Credits <BR> FROM CourseInformation<BR> WHERE CourseNo NOT IN <BR> (SELECT SelectedCourses FROM GraduationPlan WHERE StudentID = '" & Session("id") & "')<BR> ORDER BY CourseNO<BR>[/code]

That's not to mention...
That your query shouldn't return the right results anyway! <BR><BR>It's the same as doing <BR>[code language="TSQL"]<BR>SELECT CourseNo AS Course, <BR> CourseInformation.CourseName AS Name, <BR> CourseInformation.CourseCredits AS Credits <BR> FROM CourseInformation<BR>[/code]<BR><BR>Because, let's say that you have five courses (1,2,3,4,5) and the student has signed up for three of them. (1,3,5)<BR>It's see that 1 = 1, so that won't go in the result set.<BR>Then, it'll see that 1 != 3, so 1 *will* go in the result set.<BR>Then, it'll see that 1 != 5, so 1 will go in the result set again! <BR>Then, it'll see that 2 != 1, so 1 *will* go in the result set.<BR>Then, it'll see that 2 != 3, so 1 *will* go in the result set.<BR>Then, it'll see that 2 != 5, so 1 *will* go in the result set.<BR><BR>Can you see where this is headed? <BR><BR>So, doing a distinct will just reduce the resultset to one copy of each row, instead of (in this instance) two copies of each row when the student is taking that course, and three when the student isn't.

Grumble grumble...
That's what I get for copying and pasting. <BR><BR>That should have been:<BR>Five courses {1,2,3,4,5}<BR>The student is taking 3 {1,3,5}<BR><BR>It's see that 1 = 1, so that won't go in the result set.<BR>Then, it'll see that 1 != 3, so 1 *will* go in the result set.<BR>Then, it'll see that 1 != 5, so 1 will go in the result set again! <BR>Then, it'll see that 2 != 1, so 2 will go in the result set.<BR>Then, it'll see that 2 != 3, so 2 will go in the result set.<BR>Then, it'll see that 2 != 5, so 2 will go in the result set.<BR>Then, it'll see that 3 != 1, so 3 will go in the result set.<BR>Then, it'll see that 3 = 3, so 3 will *not* go in the result set.<BR>Then, it'll see that 3 != 5, so 3 will go in the result set *again*!

RE: Really though...
I had the exact same thing that you wrote but where you had NOT IN I had <>. Why Would the not equals work. I am just confused as to why that would not work?<BR><BR>thanks<BR>

It's because...
As I explained in my earlier posts, <> works on a row by row basis. NOT IN, however works at the level of the set. <BR><BR>Again, you have your set of courses {1,2,3,4,5}<BR>And the coureses that a particular student has registered for. {1,3,5}<BR><BR>When you use IN, or NOT IN, the sets will be directly compared. <BR>We can see that the when you compare {1,2,3,4,5} to {1,3,5}, {2,4} are the elements of set one that don't exist in set to, so that's our answer. <BR><BR>When you use the <> operator, it has to compare *eash element* of set one, to *each element* of set two.<BR><BR>So, with {1,2,3,4,5} and {1,3,5} you have: <BR>1 <> 1 is false.**This *won't go in the resultset.<BR>1 <> 3 is true. This *will* go in the resultset.<BR>1 <> 5 is true. This *will* go in the resultset.<BR>2 <> 1 is true. This *will* go in the resultset.<BR>2 <> 3 is true. This *will* go in the resultset.<BR>2 <> 5 is true. This *will* go in the resultset.<BR>3 <> 1 is true. This *will* go in the resultset.<BR>3 <> 3 is false.**This *won't go in the resultset.<BR>3 <> 5 is true. This *will* go in the resultset.<BR>4 <> 1 is true. This *will* go in the resultset.<BR>4 <> 3 is true. This *will* go in the resultset.<BR>4 <> 4 is true. This *will* go in the resultset.<BR>5 <> 1 is true. This *will* go in the resultset.<BR>5 <> 3 is true. This *will* go in the resultset.<BR>5 <> 5 is false.**This *won't go in the resultset.<BR><BR>So you see that when you do a row by row comparision, you *are* excluding the classses that they're registered for, but only when you're directly comparing the courses that they're registered for to *that* course in the CoursesInformation list! That course (which the student is registered for) *still* has to be compared to all of the *other* courses in the CourseInformation list, and it *won't* be equal to any of them, so it'll be added to the resultset for each of those comparisions.<BR><BR>Make any more sense this time around or still clear as mud? <BR>
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

