Looking for a better way

[jecker]

I am currently working on a project and I am wonder if there is a better method for accomplishing the same result. I have three mysql tables Area (idArea, AreaName), Question (idQuestion, fkArea, QuestionName), and Responses (fkArea, fkQuestion, responseDate, userResponse). I would like to combine all three tables with the following results: group all responses by fkArea, responseDate, and fkQuestion, but I need to list every question for every date and every area. Currently, I am using several nested 'for loops', is there a better method?

[Barand]

Use a single query with JOINs, then you only need a single loop to process the results

SELECT a.AreaName
    , r.responseDate
    , q.QuestionName
    , r.userResponse
FROM Area a
    INNER JOIN Question q ON a.idArea = q.fkArea
    LEFT JOIN Responses r ON q.idQuestion = r.fkQuestion
ORDER BY a.AreaName, r.reponseDate, q.QuestionName

I used a LEFT JOIN on reponses in case there are questions with no responses.

[jecker]

Thank you for your response, but this does not produce the result I am looking for. I need to generate a page that list all of the questions and their corresponding responses, if available, and grouped by date. Example: If the user has selected Nov 7, 2014 through Nov 9, 2014 the list should display Nov 7, all of the questions, and the corresponding responses for that day, Nov 8, all of the questions, and the corresponding responses for that day, and Nov 9, all of the questions, and the corresponding responses for that day.

[Barand]

My apologies for my shortcomings in the psychic telepathy department

[NotionCommotion]

My apologies for my shortcomings in the psychic telepathy department

 

You better work on that