-1

I have tables "course" and "student" with n-n relationship,so I create another table name "course_student" to handle that.

--Table course--
    .id            //primary key 
    .name  ```
--Table student--
    .id.          //primary key
    .name
--Table course_student
    .course_id   //primary key + foreign key course(id)
    .student_id  //primary key + foreign key student(id)

I try to insert values into "course_student" but it replies with wrong syntax

INSERT INTO course_student(course_id , student_id ) 
VALUES (
SELECT id FROM course WHERE name = "K01", 
SELECT id FROM student WHERE name = "Duy"   
);

Can you help me resolve this?

1

You must use INSERT .. SELECT:

INSERT INTO course_student ( course_id , student_id ) 
SELECT course.id, student.id
FROM course 
CROSS JOIN student 
WHERE course.name = 'K01'
  AND student.name = 'Duy';
5
  • 1
    As a note to the OP, add a unique constraint for name in course and student.
    – Lennart
    Jul 27 at 9:19
  • Oh I understand now. Thank you so much Jul 27 at 11:41
  • @Lennart according to the question (or what I understand), the PK is (course_id, student_id) Jul 28 at 11:34
  • Yes, but I mean in the course table and in the student table. If several courses are named the same, the insert is likely to result in something unwanted
    – Lennart
    Jul 28 at 11:38
  • The question assumes that each course/student can be identified by its name value. If not then the question makes no sense.
    – Akina
    Jul 28 at 11:40
1

Each sub-SELECT needs its own parentheses:

INSERT INTO course_student(course_id , student_id ) 
VALUES (
    ( SELECT id FROM course WHERE name = "K01" ), 
    ( SELECT id FROM student WHERE name = "Duy" )  
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.