MySQL Query to display information in certain way

Question

I'm having a difficult time trying to do a query to display results in a certain way. What I need is to display all user answers in a single row per user, I cannot concatenate all the answers because my program needs to access the result of every answer and for what I understand concatenation will return a string.

This is how the tables are defined:

Here's where a user's information is stored. Users

+------+---------+-------------+
| u_id | email   | name        |
+------+---------+-------------+
|    1 | bob@b   | bob         |
|    2 | [email protected] | sam         |
|    3 | ra@se   | steve       |
|    4 | tv@we   | rob         |
|    5 | tr@sd   | ted         |
|    6 | qw@as   | john        |
+------+---------+-------------+

Questions
+------+---------+-------------+
| q_id | question_name         |
+------+---------+-------------+
|    1 | Age range?            |
|    2 | Do you use Amazon?    |
|    3 | Do you use Facebook?  |
|    4 | Interested in toys?   |
+------+---------+-------------+

These are the possible answers a user can choose.

Option_Choices
+------+---------+-------------+
|oc_id | opt_choice_name       |
+------+---------+-------------+
|    1 | YES                   |
|    2 | NO                    |
|    3 | 18-24 Years           |
|    4 | 25-35 Years           |
|    5 | Very Interested       |
|    6 | Not Interested        |
+------+---------+-------------+

Here's where a question is related with every possible answer for that specific question.

Question_Options
+------+---------+-------------+
|qo_id | q_id    | oc_id       |
+------+---------+-------------+
|    1 | 1       | 3           | // Age range?: 18-24
|    2 | 1       | 4           | // Age range?: 25-35
|    3 | 2       | 1           | // Do you use Amz? Yes
|    4 | 2       | 2           | ...
|    5 | 3       | 1           | ...
|    6 | 3       | 2           | ...
|    7 | 4       | 5           | ...
|    8 | 4       | 6           |
+------+---------+-------------+

Here's where the answers given by every user are stored.

Answers
+------+---------+-------------+
| a_id | u_id    | qo_id       |
+------+---------+-------------+
|    1 | 1       | 2           |
|    2 | 1       | 4           |
|    3 | 1       | 6           |
|    4 | 1       | 7           |
|    1 | 2       | 1           |
|    2 | 2       | 3           |
|    3 | 2       | 6           |
|    4 | 2       | 8           |
|    1 | 3       | 2           |
|    2 | 3       | 4           |
|    3 | 3       | 5           |
|    4 | 3       | 8           |
+------+---------+-------------+

What I need now is to build a query to display the information in a similar manner as this:

+---------+-------------+-------------+-------------+---------------------+
| email   | Age range?  | Use Amazon? | Use FB?     | Interested in toys? |
+---------+-------------+-------------+-------------+---------------------+
| bob@b   | 18-24 Years |    YES      |    NO       | Very Interested     |
| [email protected] | 25-35 Years |    NO       |    YES      | Not Interested      |
| ra@se   | 25-35 Years |    NO       |    YES      | Not Interested      |
| tv@we   | 18-24 Years |    YES      |    YES      | Not Interested      |
| tr@sd   | 18-24 Years |    YES      |    NO       | Not Interested      |
| qw@as   | 25-35 Years |    YES      |    YES      | Very Interested     |
+------+---------+--------------------+-------------+---------------------+

But using just JOIN I get something like this"

+---------+----------------+-----------------+
| email   | question_name  | opt_choice_name |
+---------+----------------+-----------------+
| bob@b   | Age range?     |  18-24 Years    |
| bob@b   | Use Amazon?    |    YES          |
| bob@b   | Use FB?        |    NO           |
| bob@b   | Inter in toys? |Very Interested  |
| [email protected] | Age range?     |  25-35 Years    |
| [email protected] | Use Amazon?    |    NO       |
| [email protected] | Use FB?        |    YES          |
| [email protected] | Inter in toys? | Not Interested  |

   ...         ...               ...
+---------+----------------+-----------------+

I tried using pivots with a query like this:

SELECT u.email,
(CASE q.question_name WHEN q_id = 1 THEN oc.opt_choice_name ELSE 0) AS 'Age_Range',
(CASE q.question_name WHEN q_id = 2 THEN oc.opt_choice_name ELSE 0) AS 'Amazon',
(CASE q.question_name WHEN q_id = 3 THEN oc.opt_choice_name ELSE 0) AS 'FB',
(CASE q.question_name WHEN q_id = 4 THEN oc.opt_choice_name ELSE 0) AS 'Toys'
FROM Users u
INNER JOIN Ansers a ON u.u_id = a.u_id
INNER JOIN Question_Options qo ON a.qo_id = qo.qo_id
INNER JOIN Questions q ON qo.q_id = q.q_id
INNER JOIN Option_Choices oc ON qo.oc_id = oc.oc_id
GROUP BY u.email

But I'm getting mixed results.

Any suggestion will be greatly appreciated.

Thanks.

I tried using MAX

SELECT u.email,
MAX( CASE q.question_name WHEN q.question_id = 1 THEN oc.option_choice_name ELSE 0 END) AS 'AgeRange',
MAX( CASE q.question_name WHEN q.question_id = 2 THEN oc.option_choice_name ELSE 0 END) AS 'Amazon',
MAX( CASE q.question_name WHEN q.question_id = 3 THEN oc.option_choice_name ELSE 0 END) AS 'FB',
MAX( CASE q.question_name WHEN q.question_id = 4 THEN oc.option_choice_name ELSE 0 END) AS 'toys',
FROM Users u
INNER JOIN Answers a ON u.u_id = a.u_id
INNER JOIN Question_Options qo ON a.qo_id = qo.qo_id
INNER JOIN Questions q ON qo.q_id = q.q_id
INNER JOIN Option_Choices oc ON qo.oc_id = oc.oc_id
GROUP BY u.email

But the results I'm getting are like:

+---------+-------------+-------------+-------------+---------------------+
| email   | Age range?  | Use Amazon? | Use FB?     | Interested in toys? |
+---------+-------------+-------------+-------------+---------------------+
| bob@b   | YES         |    YES      |    NO       |          NO         |
| [email protected] | YES         |    YES      |    YES      | YES                 |
| ra@se   | YES         |    YES      |    YES      | YES                 |
| tv@we   | YES         |    YES      |    YES      | YES                 |
+------+---------+--------------------+-------------+---------------------+

Tabulating the result is a UI function, not a database function (at least not for mysql). — thebjorn
– thebjorn, Commented Dec 27, 2013 at 16:00
I agree. Just because you can, it doesn't mean you should (although sticking MAX in there will fix it! E.g. MAX(CASE...)) — Strawberry
– Strawberry, Commented Dec 27, 2013 at 16:02
Hm, well consider providing proper DDLs (and/or an sqlfiddle) and I'm sure someone will show you. — Strawberry
– Strawberry, Commented Dec 27, 2013 at 16:09
@Sebastian no, you get fastest performance if the database layer performs well-optimizable queries, returning only the necessary data. Trying to format the data according to one single output format only means that you'll lose generality of the data, which, if we're talking about heavy usage scenarios, will destroy your database's cache. You should instead use a different server (either a UI server and/or a data-layer server) to tabulate your data in a form that is convenient for creating html. — thebjorn
– thebjorn, Commented Dec 27, 2013 at 16:32

Ahmed Siouani · Accepted Answer · 2013-12-27 21:35:15Z

1

Although you could do this with MySQL, but it's not that efficient because MySQL indexes rows not columns.

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs.

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

The way most people would approach this is to enumerate all of the questions with a JOIN such as you have:

+---------+----------------+-----------------+
| email   | question_name  | opt_choice_name |
+---------+----------------+-----------------+
| bob@b   | Age range?     |  18-24 Years    |
| bob@b   | Use Amazon?    |    YES          |
| bob@b   | Use FB?        |    NO           |
| bob@b   | Inter in toys? |Very Interested  |
| [email protected] | Age range?     |  25-35 Years    |
| [email protected] | Use Amazon?    |    NO       |
| [email protected] | Use FB?        |    YES          |
| [email protected] | Inter in toys? | Not Interested  |

   ...         ...               ...
+---------+----------------+-----------------+

And then in your PHP you would do something like (disclaimer: untested code):

$i = 0;
echo "<table><tr><th>Age range?</th><th>Use Amazon?</th><th>Use Facebook?</th><th>Interested in Toys?</th></tr><tr>";
foreach ($results as $column) {
    echo "<td>".$column."</td>"; 
    if ($i != 0 && $i % 4) // Just arbitrarily chose 4, but it would be however many columns you have
        echo "</tr><tr>";

    $i++;
}
echo "</tr></table>";

edited Dec 27, 2013 at 21:35

Ahmed Siouani

13.9k12 gold badges63 silver badges73 bronze badges

answered Dec 27, 2013 at 16:32

Alex W

38.5k13 gold badges114 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sebastian Over a year ago

Thanks. I'll do it like that, using the php to get the answers.

Anubhav · Accepted Answer · 2013-12-27 16:38:25Z

0

Another way to solve this is create temporary tables separately for Every question and finally Create one mysql query to get all data horizontally at once.

http://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm

answered Dec 27, 2013 at 16:38

Anubhav

1,6234 gold badges18 silver badges32 bronze badges

Collectives™ on Stack Overflow

MySQL Query to display information in certain way

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related