CODEWITHSUNDEEP
mysqlsql
Neve12ende12
Neve12ende12

Reputation: 1194

SQL Left Join Foreign Key Both Tables

I have a query in which I am joining a table of answers to their questions. Both tables have a foreign key that links back to a user table to determine who asked and who answered the question.

My question is, how can I modify the following query to make product_question.userID AND product_answer.userID show the name of the user from the User table instead of just the ID?

                    SELECT `project_question`.`id` AS question_id, `project_question`.`question`, `project_question`.`userID` AS askedBy, 
                           `project_question`.`created` AS question_created, `project_answer`.`id` AS answer_id, 
                           `project_answer`.`answer`, `project_answer`.`userID` AS answeredBy, 
                           `project_answer`.`accepted`, `project_answer`.`created` AS answer_created
                      FROM `project_question`
                 LEFT JOIN `project_answer`
                        ON `project_question`.`id` = `project_answer`.`questionID`
                     WHERE `project_question`.`projectID` = $args[0]
                       AND `project_question`.`projectPhase` = 2

Upvotes: 0

Views: 615

Answers (1)

mucio
mucio

Reputation: 7119

You can use two joins to the same table and aliases to distinguish them:

      SELECT `project_question`.`id` AS question_id, 
             `project_question`.`question`, 
             q_user.`userName` AS askedBy, 
             `project_question`.`created` AS question_created, 
             `project_answer`.`id` AS answer_id, 
             `project_answer`.`answer`, 
             a_user.`userName` AS answeredBy, 
             `project_answer`.`accepted`, 
             `project_answer`.`created` AS answer_created
        FROM `project_question`
   LEFT JOIN `project_answer`
          ON `project_question`.`id` = `project_answer`.`questionID`
  INNER JOIN `User` AS q_user
          ON `project_question`.`userID` = q_user.`userID`
  INNER JOIN `User` AS a_user
          ON `project_answer`.`userID` = a_user.`userID`
       WHERE `project_question`.`projectID` = $args[0]
         AND `project_question`.`projectPhase` = 2

Upvotes: 2

Related Questions