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.
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.
select * from sentilists as a
join users as b on a.created_by_user_id = b.user_id
join questions as c on a.theme_id = c.theme_id ..... and so on
I am not sure how theme and questions are related so maybe you can clarify that but I guess a multijoin would work in your situation.