Skip to content
Advertisement

Table Join in SQL not returning expected results

My database design is seen as below: DB Design

I’m interested in finding out what questions are in what questionnaire.

So I tried the below in order to output what questions were held inside of the questionnaire Welcome Questionnaire, however I get no results back in the rows:

SELECT `questionnaire`.`questionnaireName`, `questionnaireQuestions`.`questionID`,`question`.`question`
FROM `questionnaire`
INNER JOIN `questionnaireQuestions` ON `questionnaire`.`questionnaireID`=`questionnaireQuestions`.`questionID`
INNER JOIN `question` ON `questionnaire`.`questionnaireID` = `question`.`questionID`
WHERE `questionnaire`.`questionnaireName` = "Welcome Questionnaire";

I assumed I had to carry out 2 inner joins on the questionnaireID, could anyone highlight where I’m going wrong here.

As I should be getting this:

questions should be returned

Advertisement

Answer

From your table structure shown in the diagrams, you are joining on the wrong fields. You should join questionnaireQuestions to questionnaire on questionnaireID and questionnaireQuestions to question on questionID i.e.

SELECT `questionnaire`.`questionnaireName`, `questionnaireQuestions`.`questionID`,`question`.`question`
FROM `questionnaire`
INNER JOIN `questionnaireQuestions` ON `questionnaire`.`questionnaireID` = `questionnaireQuestions`.`questionnaireID`
INNER JOIN `question` ON `questionnaireQuestions`.`questionID` = `question`.`questionID`
WHERE `questionnaire`.`questionnaireName` = "Welcome Questionnaire";

Note that using table aliases can make your query easier to read; and backticks are only required to escape special characters in table/column names, so can in general be omitted:

SELECT qa.questionnaireName, qq.questionID, qs.question
FROM questionnaire qa
INNER JOIN questionnaireQuestions qq ON qa.questionnaireID = qq.questionnaireID
INNER JOIN question qs ON qq.questionID = qs.questionID
WHERE qa.questionnaireName = "Welcome Questionnaire";
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement