There are two tables: QUESTIONS AND ANSWERS :

QUESTIONS

PID ID (PK)
QUESTION TEXT
ANSWER (FK)
CORRECT ANSWER (FK)


ANSWERS

ID_TAKEN (PK)
REPLY TEXT
VID_ID (FK)

How is it possible to implement such a request?

INSERT INTO ВОПРОСЫ (ТЕКСТ ВОПРОСА, ОТВЕТ, ПРАВИЛЬНЫЙ ОТВЕТ) VALUES ("ТЕКСТ ВОПРОСА", ПОДЗАПРОС(СОЗДАТЬ ЗАПИСЬ В ТАБЛИЦЕ ОТВЕТЫ И ОТДАТЬ ЕГО ID), 1)

Probably the train of thought is wrong. But I am straight and I do not know how else. I will be glad to any help

  • @strangeqargo MySQL 5.6. well, if I understood you correctly - user200192
  • 2
    similar to the XY question . - jfs
  • one
    Yes. considering that the text of the question is slightly googled, it seems that someone (the author) either listened very badly or someone (the lecturer) spoke very badly - strangeqargo
  • one
    @ user200192, one request in MySQL will not work. - cheops
  • @jfs whatever it is, I have no other thoughts in my head how to solve this problem, so I ask if not to help, then at least send the right path - user200192

1 answer 1

Schematically, the procedure for working with tables may look as follows

 -- Сначала вставляем вопрос INSERT INTO ВОПРОСЫ (ТЕКСТ ВОПРОСА, ОТВЕТ, ПРАВИЛЬНЫЙ ОТВЕТ) VALUES ("ТЕКСТ ВОПРОСА", NULL, NULL); -- Получаем идентификатор вопроса SET @id = LAST_INSERT_ID(); -- Вставляем ответы INSERT INTO ОТВЕТЫ (NULL, "ТЕКСТ ОТВЕТА", @id); SET @answer_id = LAST_INSERT_ID(); INSERT INTO ОТВЕТЫ (NULL, "ТЕКСТ ОТВЕТА", @id); SET @answer_id = LAST_INSERT_ID(); INSERT INTO ОТВЕТЫ (NULL, "ТЕКСТ ОТВЕТА", @id); SET @answer_id = LAST_INSERT_ID(); INSERT INTO ОТВЕТЫ (NULL, "ТЕКСТ ОТВЕТА", @id); SET @answer_id = LAST_INSERT_ID(); -- Обновляем FK ВОПРОСЫ.ОТВЕТ UPDATE ВОПРОСЫ SET ОТВЕТ = @answer_id WHERE id = @id; -- Вставляем правильный ответ INSERT INTO ОТВЕТЫ (NULL, "ТЕКСТ ПРАВИЛЬНОГО ОТВЕТА", @id); -- Получаем идентификатор правильного ответа SET @answer_right_id = LAST_INSERT_ID(); -- Обновляем FK ВОПРОСЫ.ПРАВИЛЬНЫЙ ОТВЕТ UPDATE ВОПРОСЫ SET ПРАВИЛЬНЫЙ ОТВЕТ = @answer_right_id WHERE id = @id;

For real use, Russian names of columns and tables should be replaced with English equivalents that do not contain spaces.

  • it is not clear how the response id gets to the questions table? (or is it implied that after each insert , requests are executed similar to the shown requests for the correct answer?) Of course, the scheme can be changed (if there are no special needs, it is better to use the normalized scheme) so that adding the answer does not require UPDATE. - jfs
  • I agree, it is not clear that in general there should be in the QUESTION. ANSWER field, I left this field not filled. I myself would not start such a field in the table. - cheops
  • in this case, the QUESTION. ANSWER must contain the id from the corresponding entry in the answers as well as for the correct answer. That is, your answer does not imply that mysql does something magical and automatically fills the QUESTION. ANSWERS of the foreign key, when creating an entry in the ANSWERS table with the corresponding QUESTIONS.id and therefore there are not enough requests to fill the QUESTIONS. ANSWER column. - jfs
  • @jfs, POLL. ANSWER must contain id which of the 5 records from the ANSWER table corresponding to this question? What is your understanding of the purpose of this field? Honestly, I would listen to the author’s version, but if you understand his idea well, please explain. - cheops
  • as I said, the scheme should be changed, but if you use the given scheme, you should fill in the fields to simulate a pseudo-request from a question (even if it is meaningless (it does not help the author of the question to solve a real problem) —it can show future visitors implement the sql subquery "). You can put the most recent response id, or even create a new entry for each answer in questions (again, meaningless, but shows how to create related sql queries) - jfs