Print specific values from dictionary with a specific key name
Siten0308 - Jun 20 2019 01:43 PM
How to make code run differently depending on the platform it is running on?
xarzu - Apr 05 2019 09:17 AM
How do I set a breakpoint in an attached process in visual studio
xarzu - Apr 04 2019 11:47 AM
Recent Blog Entries
Recent Status Updates
- Managed C++
- Visual Basic 4 / 5 / 6
- linked list
- hello world
Purely SQL Worries
Posted 22 April 2010 - 12:46 AM
Basically, teachers are choosing which students they can see. They can add and remove students at will. So the table looks like:
Now, if Phil wanted to remove Student 1115 and then Trent wanted to remove Student 1111 there will be an empty column in the middle. I want my program to remove that column and then rename Student5 to Student4 (in this particular case) just to keep it all tidy.
Moving the contents from Student5 to Student4 and then dropping Student5 is an option and that would be okay in this example, but in reality there is more than 5 students and more than 3 students. What if the empty column was Student4 and it went up to Student17 with there being 9 different teachers? It would take a very long time for it execute all of that SQL.
Will I just has have to put up with a messy table?
Posted 22 April 2010 - 01:05 AM
ID autoinc --indexed, primary key, unique
Name varchar(180000) --index this too??
(optional) ID autoinc --this ought to have an index/primary
TeacherID int --INDEX THIS ONE!! this is the number of the teacher, and this is how you link them 2getha
StudentID int --this is where you put your 1115, etc
StudentRank int --if you want these students to have a certain order, as in student 1, etc, you have to add this number somewhere and you can SORT on it. INDEX this.
THis is a link table, linking CERTAIN records wron teachers to CERTAIN records from sudents. Both students and teachers can occur multiple times in here.
Now, to get all students of fred, you do
SELECT Students.Name FROM Teachers INNER JOIN TeacherSudents ON Teachers.ID=TeacherStudents.TeacherID INNER JOIN Students ON TeacherStudents.StudentID=Students.ID WHERE Teachers.Name='Fred' ORDER BY TeacherStudents.StudentRank;
will return something like
They're in order, student 1=Jake, Student 2=Cindy
I'm not sure if this is correct SQL. Mess with it if you need . But something LIKE this surely works. And the plus is you can add any number of students AND each teacher gets their own private list.
Anyway, if this seems daunting to you, there are many places where you can find info on doing this stuff. Go to W3Schools SQL for SQL help. Also look for database schema normalization.
To get you started, the above are links... Just in case you missed that.
Edited by Firebird_38, 22 April 2010 - 07:04 AM.
Posted 22 April 2010 - 01:09 AM