从6个表构建一个mysql查询以减少php的使用

Question

First, I don't know if this is the right exchange. I've been working on a system for requesting feedback in a score from 1 to 5 to people around you, but I'm having trouble building the query for displaying the result. Another dev has already made the tables, so I have to work with this. I have this mysql-database.

These are the simplified tables, so you get the idea. These are static:

users
user_id || user_name || user_email

questions
question_id || theme_id || question

themes
theme_id || theme_title

And these are the ones where the users can add records. A user selects a theme and sends requests to other users.

sentlists
list_id || created_by_user_id || theme_id

requests
request_id || to_user_id || sent_list_id || answered

responses
response_id || request_id || rating 

How can I build a query (if at all possible) that, when given a list_id, results in a result like this:

user_name || question name 1 || question name 2 || question name 3 || question name 4
Michael || 2 || 3 || 1 || 4

I've been breaking my head over this for the past couple hours, and I can't come up with a good solution. I could fall back to PHP, but that would result in multiple queries for one result.

Answer 1

If my cross tab assumption is correct, you are better off assembling the final results in code. In which case, it is probably unlikely you'll need multiple queries; the obvious JOINs with a helpful ORDER BY (such as ORDER BY user_name, question_name, answer) should make the code portion relatively simple.

You cannot vary result fields in a single query. You can use code to create queries dynamically, but that often ends up more work than it is worth.