Columns with multiple values ?

Hi,
The values I need to store in the table are

Student ID
Student Name
Subjects

The "Student ID" is the primary key.

A student can take more than 1 subject.

For example:
Student ID: 100
Student Name: Kelly Preston
Subjects: Geography, History, Math

How can I store these values in a database table?
I know the normal "INSERT" statement, but how would I store the multiple subjects for a single student ID?

My "Student ID" is auto generated. If I create a new row for each subject, the Student ID will be different for each subject, which I dont want.

Or I can create a new field called "RowNumber" and keep that the primary key..
For example:

Row Number StudentID StudentName Subject
1 100 Kelly Geography
2 100 Kelly History
3 100 Kelly Math

If this is the only way to store the multiple sibjects, then for a given student ID (say 100), how can I retreieve the associated name and subjects? What is the query for that?...If this is the only way to store the multiple sibjects...It isn't. I'm afraid that your design is really flawed. You would want to be certain that your design is correct before you even start thinking about SQL.

Have a read of this and then come back and let us know what you think your design should now be
http://r937.com/relational.html

EDIT: Oops - first post - welcome to the forum :D

HTH|||Pay particular attention to the section on many-to-many relationships, which I think is applicable to your situation.|||Table # 1: Student Details
---------
Student ID (primary key)
Student Name

Table # 2: Subjects
-------
RowID (primary key)
Student ID
Subject

For example:
We have 2 students, Kelly and Mary.
Kelly's subjects are History and Math
Mary's subjects are Math and English

Table # 1
----
Student ID Student Name
100 Kelly
200 Mary

Table # 2:
----
RowID StudentID Subject
1 100 History
2 100 Math
3 200 Math
4 200 English

When I want to display Kelly's details:
select * from 'Student Details' where StudentID = '100' UNION select * from Subjects where StudentID = '100'

The SQL query might not be of the right syntax, but is this idea right?

I believe this is a one-to-many relationship..|||No. You do not need a UNION query to do this, and it won't even work unless the designs of the two tables are identical.

Again, your database design is flawed. Please read the section on how to create many-to-many relationships.|||The SQL query might not be of the right syntax, but is this idea right?You've got a few details left to resolve, but you've definitely got the right idea. You want to use a JOIN instead of a UNION, but you're headed the right way now.

-PatP