Re: Complicated query
Date: Sat, 6 Dec 2014 16:53:43 +0000 (UTC)
Message-ID: <m5vcan$b7u$1_at_dont-email.me>
On Sat, 06 Dec 2014 08:28:18 -0500, Shelly wrote:
> I have finally come across a desired query that I have no idea on how to
> do -- or if it is even possible. Here is the scenario:
>
> Table Instances ===============
> id column1 column2 (and more)
>
> Table Questions ===============
> Id Question Text
>
> Table Answers =============
> Id QuestionId (Id in Questions)
> InstanceId (Id in Instances)
>
> Table Questions has Q entries. (Lets say just three for now.) What I
> want is to pull where each record contains:
>
> Table Instances has N entries.
>
> Table Answers has Q x N entries.
>
> I want to pull N records where each record has:
> column1, column2, Answer1, Answer2, Answer3
>
> In other words, I want all the answers (Q of them) for a given instance
> to be in a single returned record, not one answer in each of Q records
> returned.
Table questions doesn't seem to feature in your result at all.
Reducing the problem as I perceive it to it's minimal form, does the following help.
mysql> select * from instances;
+----+----+
| id | t1 |
+----+----+
| 1 | i1 | | 2 | i2 | | 3 | i3 |
+----+----+
3 rows in set (0.00 sec)
mysql> select * from answers;
+------+------+
| inst | ans |
+------+------+
| 1 | q1a1 | | 1 | q1a2 | | 1 | q1a3 | | 2 | q2a1 | | 2 | q2a2 | | 2 | q2a3 | | 3 | q3a1 | | 3 | q3a2 | | 3 | q3a3 |
+------+------+
9 rows in set (0.00 sec)
mysql> select instances.t1, group_concat(answers.ans) from instances join answers on instances.id = answers.inst group by instances.id;
+----+---------------------------+
| t1 | group_concat(answers.ans) |
+----+---------------------------+ | i1 | q1a1,q1a2,q1a3 | | i2 | q2a1,q2a2,q2a3 | | i3 | q3a1,q3a2,q3a3 | +----+---------------------------+
3 rows in set (0.00 sec)
-- Denis McMahon, denismfmcmahon_at_gmail.comReceived on Sat Dec 06 2014 - 17:53:43 CET