Search code examples
phpmysqlrelational-databaserdbms

JOIN or subquery in SQL to grab data out of various tables needed

I need to pull out some data from various tables using PHP prepared statements and MySQL.

The items I need to plot the data into a graph are:

tblstudent.studentID tblquestionnaire.questionnaireID tblstudentAnswer.answer

The database design looks like this with my table joins. DB Design

I have attempted to use INNER JOIN's, however I cannot join tblquestionnaire into it as I do not share a key with that table and the student table or with the studentAnswer table.

Any guidance would be much appreciated in how I get those pieces of information out within an SQL query.

Solution

  • You are overthinking it.

    to get all student Ids, with their answers and the questionaire ID.

    The following query is enough.

    SELECT
    sta.studenID
    ,qq.questionnaireID
    ,sta.answer
FROM 
  studentAnswer sta
INNER JOIN 
    questionnaireQuestions qq ON sta.questionnID =  qq.questionnID