Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    2

    Unanswered: 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?
    Last edited by srilatha26; 05-23-06 at 04:52.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by srilatha26
    ....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

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pay particular attention to the section on many-to-many relationships, which I think is applicable to your situation.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2006
    Posts
    2

    How does this design look?

    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..

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by srilatha26
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •