Jump to content

Check out our Community Blogs

Register and join over 40,000 other developers!

Recent Status Updates

View All Updates

- - - - -

Purely SQL Worries

  • Please log in to reply
16 replies to this topic

#13 Ilikestring


    CC Newcomer

  • Just Joined
  • PipPip
  • 17 posts

Posted 22 April 2010 - 12:46 AM

Yeah, I'm trying to actually change the name of the column, and it doesn't seem possible.
Basically, teachers are choosing which students they can see. They can add and remove students at will. So the table looks like:

Posted Image

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?
  • 0

#14 Firebird_38


    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 117 posts

Posted 22 April 2010 - 01:05 AM

No. You need to add a table.

Table schemas:

Teachers table:
ID autoinc --indexed, primary key, unique
Name varchar(180000) --index this too??

TeacherStudents table:
(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
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.

Good luck.

Edited by Firebird_38, 22 April 2010 - 07:04 AM.

  • 0

Posted Image

#15 Firebird_38


    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 117 posts

Posted 22 April 2010 - 01:09 AM

One more thing about these "relationships". They are the bases of "relational databases", which is what SQL is designed for. Delphi provides built in support for these relations, using master/slave properties in your datasets. You can set them in the object inspector. Again, plenty of help is available to you. Look in the help or google it.
  • 0

Posted Image

#16 WingedPanther73


    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 22 April 2010 - 07:02 AM

SQL Tutorial
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/

#17 Firebird_38


    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 117 posts

Posted 22 April 2010 - 07:04 AM

SQL Tutorial

lol. I posted that 2 posts back :)
  • 0

Posted Image

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download