mySQL: how to fill this questions - answers table

Question

hi,

I have 5 tables

Questions_Answers

AID  |  QID  |  otherStuff  |  Username  |  UserID  |  otherStuff

Users

UserID  |  Username

Node

ID  |  Type  |  UserId 

Questions

ID  |  oldQ_ID 

Answers

ID  |  Question_Link

I have to fill the empty table Questions_Answers given the information in the other tables.

The Question_Answer table is supposed to contain, for each answer (row), the question it belongs to. Also, for each answer the username and userId.

The Node table contains both questions and answers. The Type field has 2 possible values 'question' and 'answer' specifying if that row is indeed a question or an answer. It contains the usedId who wrote the node.

In the Questions and Answers table, the ID fields are the new IDs.(I've imported these nodes in a new system). These are the IDs that should be added to the question_answer table (AID and QID fields, for answers and questions respectively).

In the Questions table, the oldQ_ID should be only used to assign the answers to their questions, and not added to the Questions_Answer table.

The Question_Link in the Answers table links each answer to its question and refers to the old ID of each question (oldQ_ID) (!).

One more thing. I need to insert the fields in Questions_Answers table in the correct fields, skipping the useless ones.

Thanks..

Candidate solution:

INSERT INTO node_comments (cid, nid, name, uid)
  SELECT a.nid, q.nid, u.name, u.uid
  FROM node AS n
  JOIN content_type_answer AS a ON a.nid  = n.nid
  JOIN content_type_forum AS q ON q.field_oldqid_value = a.field_qlink_value
  JOIN users AS u ON u.uid = n.uid
  WHERE n.type = "answer";

Answer 1

UPDATE:
I've updated the answer to use Node.ID as the join relation for the Questions table. I think this is close to what you are after, and it shouldn't be too tricky to change if it's not quite right:

INSERT INTO Questions_Answers (AID, QID, Username, UserID)
  SELECT a.ID, q.ID, u.Username, u.UserID
  FROM Node AS n
  JOIN Users AS u ON u.UserID = n.UserID
  JOIN Questions AS q ON q.oldQ_ID = n.ID
  JOIN Answers AS a ON a.Question_Link = q.oldQ_ID
  WHERE n.Type = "answer";

Here's what the query does:

1. For every row in Node where the Type is answer:
   • Compare Node.UserID with Users.UserID to find the matching rows from the Users table.
   • Compare Node.ID with Questions.oldQ_ID to find the matching rows from the Questions table.
   • Compare Questions.oldQ_ID with Answers.Question_Link to find the matching rows from the Answers table.
2. Select Answers.ID, Questions.ID, Users.Username, Users.UserID from the match in step 1, and insert these into the Questions_Answers table.

If you need to match Node.ID with an answer rather than a question, then you need a column like Answers.oldA_ID in the Answers table. You cannot compare Node.ID with Questions.ID, as Node.ID is the old ID and Questions.ID is the new ID.