ansaurus

Question

I need help on INSERT command, two tables

Answer 1

+1 A:

INSERT INTO GRADES (IDSUBJECT, GRADE)
  (
    (SELECT SubjID
     FROM SUBJECTS
     WHERE USERNAME = [username]
     AND SUBJECT = [subject]),
    [grade]
  )

Mind you, is there a reason you don't have the Subject ID but only the subject name? Also, do you really want the username to be a field in the subjects table and not the grades table?

DavidMWilliams 2009-07-07 15:14:06

+1 for "do you really want the username to be a field in the subjects table and not the grades table?"

Dustin Fineout 2009-07-07 15:15:54

Thank you kindly :)I think - as, obviously, you do too - that it makes more sense to have the user as a field in the Grades table because the grade is specific to the combination of user and subject. As the table design is it sounds like the institution offers a unique subject for each student.

DavidMWilliams 2009-07-07 15:20:30

Thank you very much but I keep getting "Syntax error in INSERT INTO statement". I'm not sure about where the Username field should be.

2009-07-07 15:22:57

You will need to replace the names I've used ("GRADES", "IDSUBJECT", "username", etc) with the actual names of your tables and values.What is the environment you are using? ie Is this query being entered into SQL Server's Query Analyzer, or are you executing SQL queries from within a VB app, etc. ?

DavidMWilliams 2009-07-07 15:25:41

I did replace the values. It's MS Access 2007.

2009-07-07 15:34:05

Ok, in that case the MySQL tag probably ought to be removed. I'll fire up Access in the morning and test my syntax. It may need some modification.That said, if you modified your design so username was a field in the grades table and if were retrieving the SubjectID (from a drop-down list, say) then the insert statement would be dead simple, merely INSERT INTO GRADES (SubjectID, Username, Grade) values ([SubjectID], [username], [grade]) - no need to reference the subject table at all.

DavidMWilliams 2009-07-07 15:40:26

Unfortunately I do not yet have sufficient reputation to comment on other people's answers but I just want to note the solution by AlexKuznetsov below is a bit dangerous; because he does not refer to the username field his update command will actually set the value to be the same for all users taking that subject. Also, you're not seeing a result because UPDATE will only change the value of existing rows; it must be INSERT unless you have prepopulated the GRADES table with zero grades for every subject/user comboination in advance.

DavidMWilliams 2009-07-07 15:50:26

Thank you very much. I'll try moving the field Username to Grades and see if it works.

2009-07-07 16:10:38

That won't work because if the Username is the Grades table then subjects might get duplicated because they're not attached to the user. What I'm trying to do is 1.)let a user enter his own subjects, 2.)list all user's subjects, click on one and add a grade to it, 3.)display stats for a user (subjects and grades). So if I do like you told me, then subjects are not related to a specific user so duplicated might occur.

2009-07-07 16:34:23

I solved it by doing SUBJECTS(ID,Subject,Username) and GRADES(ID,IDsubjects, Username, Grade) so first I query for a subject ID then I take that value and call another query to INSRT to Grades with SubjectID, Username, Grade. Problem solved, not really relational but anyway...thank you and everybody else for helping me!

2009-07-07 17:23:17

I still feel subjects shouldn't have username in it; you're introducing a problem where the name of each subject is listed in entirety multiple times.Perhaps a solution might be to rename the GRADES table; instead of considering it the collection of results consider it the linking of users to subjects. ie SUBJECTS (ID, Subject) and ENROLLMENTS (ID, SubjectID, Username). You could then add GRADE as a column in the ENROLLMENTS table.To me it just doesn't feel right having username in Subjects because subjects aren't one per user, and the full subject name should only be listed once.

DavidMWilliams 2009-07-08 10:25:50

Answer 2

+1 A:

I'm assuming your GRADES.IDgrades is an auto_increment key and will be automatically generated. Also, in the SELECT statement the value 'A' is where you will put your actual grade value. It isn't actually selecting this from the SUBJECTS table, simply selecting the explicit value specified. I also used 'math' and 'billy' as your values for grade and subject, respectively.

INSERT into GRADES (IDsubject, grade)
    SELECT IDsubjects, 'A'
    FROM SUBJECTS 
    WHERE username = 'billy' 
        AND subject = 'math'

This will insert a new row in GRADES with the IDsubject as selected from the IDsubjects table.

Dustin Fineout 2009-07-07 15:14:58

Thank you. I get the same error here but I'll try figure out what's wrong.

2009-07-07 15:37:14

Answer 3

+1 A:

UPDATE Grades SET grade='F' WHERE subject IN(SELECT subject FROM stackoverflow)

AlexKuznetsov 2009-07-07 15:17:40

Thank you. I get no error here, query gets executed but no values are being stored anywhere.

2009-07-07 15:37:48

insert vs. update?

CodeSlave 2009-07-15 18:16:57

Answer 4

A:

Assuming that IDGrades is automatically generated ID,

INSERT INTO GRADES (IDSubject, GRADE)
SELECT  s.IDsubjects,
        @GRADE
FROM    SUBJECTS s
WHERE   s.USERNAME = @USERNAME
    AND s.[SUBJECT] = @SUBJECT

Very strange naming convention with plural ID columns (IDSubject**s**)

van 2009-07-07 15:30:45

Thank you for your help but I don't see any changes. Perhaps this doesn't work in MS Access 2007?

2009-07-07 16:11:14

@dotnetasp35: give a real example SQL of how you executed it

van 2009-07-07 17:21:18

INSERT INTO grades (IDSubject, Grade)SELECT subjects.IDsubjects, @GRADEFROM SUBJECTS subjectsWHERE subjects.USERNAME = 'demo' AND subjects.[Subject] = 'math'

2009-07-07 19:36:38

what do you get when you run only: SELECT subjects.IDsubjects FROM SUBJECTS subjects WHERE subjects.USERNAME = 'demo' AND subjects.[Subject] = 'math' ??? -- If no rows are returned then you basically do not have a subject row for this user and this subject yet. You should create it first. Advise.

van 2009-07-07 20:09:30

ansaurus

tags:

views:

answers:

I need help on INSERT command, two tables

related questions