Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Auto-increment question

    I have a table
    CREATE TABLE response (
    rID int NOT NULL AUTO_INCREMENT,
    sID int NOT NULL ,
    qID int NOT NULL ,
    response int NOT NULL ,
    PRIMARY KEY (rID, sID, qID))

    Can i insert into this table in this format...(rID is auto increment)

    rID sID qID resonse
    _____________________
    1 1 1 sdad
    1 1 2 fsdfsd
    1 1 3 fsdfsd
    2 2 1 fdsfsdf
    2 2 2 dsfsdfsd
    ........

    If i use rID = NULL while inserting each row it would increment rID in second row i guess and so oon.
    How can i insert a row with an increment in rID when SID changes.
    Can some one please help me...
    Thanks,
    Cooldude.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can you insert those rows?

    hmmm, did you try it? i did

    i'll even share the insert statement with you --

    insert into response
    (rID, sID, qID, response)
    values (1, 1, 1, 'sdad')
    ,(1, 1, 2, 'fsdfsd')
    ,(1, 1, 3, 'fsdfsd')
    ,(2, 2, 1, 'fdsfsdf')
    ,(2, 2, 2, 'dsfsdfsd')
    ;

    how can you insert a row with an increment in rID when sID changes?

    that's a good question, i don't think you can write any sql that determines when sID "changes"

    you would have to determine that before you inserted the row

    and then what would you do when you wanted to add a second and third row for the same rID?

    insert into response
    (rID, sID, qID, response)
    values
    (2, 2, 3, 'dsfsdfsd')
    ,(null, 3, 1, 'another')
    ,(??, 3, 2, 'another')
    ,(??, 3, 3, 'another')
    ;

    the best thing to do here is not to use an auto_number for rID and simply insert the values yourself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    It looks like the sid column will always have the same value as the rid.

    If so, then what is the point of having two columns with exactly the same value?

    Ravi

  4. #4
    Join Date
    Oct 2004
    Posts
    4

    Clarification

    Hi all,
    I would like to give some clarification....
    rId is response id which means that each individual user must have a distinct id and sid is a survey id and qid is question id where each survey might have 10 questions or so.

    How can i generate rId to be distinct for each user that is submitting the survey.

    If we generate rId manually how can i know rId of the previous user and if many users r submitting the survey simultanelosly how would it know what rId value it should take.

    Any help would be appreciated.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm going to make the assumption that each user's responses to the survey are to be processed as a group

    furthermore, i assume you have some sort of application that will screen/filter/edit/process/validate these responses, and this application will know when a user's set of responses has been submitted as a group

    if the users do not already have user_ids (for example, in an anonymous survey), then all you have to do is add a new entry to a controlling "userresponse" table, logging the datetime that this group of responses were processed, and obtaining an auto_increment id value for this user

    then use this value when entering the responses into the above table

    i.e. don't use an auto_increment in the above table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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