Combining a variable amount of rows into one row?

Question

I have two tables: question and answer.

Here is a simplified version of the question table's schema:

question_id      integer PRIMARY KEY
question_title   varchar(250)
answers_id       SERIAL UNIQUE

Here is a simplified version of the answer table's schema:

answer_id        integer REFERENCES question (answers_id)
answer_text      varchar(500)
is_top_answer    boolean

I would like to accomplish three tasks in a single query:

Select a row from the question table based off of a parameter that contains a question_id.
Join exactly one row from the answer table with the row retrieved from the question's table in step 1 of the query where the two following conditions are satisfied by a row in the answer table: 1. the is_top_answer is true and 2. the answer_id equals the answers_id of the row retrieved from the question table in step 1 of the query.
Join a variable amount of rows from the answer table with the row retrieved from the question's table in step 1 of the query where the answer table's answer_id matches the answers_id of the row retrieved from the question table in step 1 of the query.

I have populated the question and answer tables with the following SQL statements:

insert into question values (1, 'Where is the best sushi restaurant?', 10);
insert into answer values (10, 'In California', 'false');
insert into answer values (10, 'In Seattle', 'true');
insert into answer values (10, 'In New York', 'false');

If I were to query the question table with a parameter that represents a question_id held in the question table, I would expect the following single row as a result:

 question_id |           question_title            | answers_id |   answer_text | is_top_answer  |   answer_text | is_top_answer  |   answer_text | is_top_answer  |
-------------+-------------------------------------+------------+---------------+----------------+---------------+----------------+---------------+----------------+
           1 | Where is the best sushi restaurant? |         10 | In Seattle    | f              | In California | f              | In New York   | f              |

Does anyone have any suggestions for accomplishing this query?

`SERIAL` sort of screams Postgres. Why is the question tagged MySQL? — Gordon Linoff, Jun 08 '15 at 03:11
@GordonLinoff Because I didn't get much assistance when I posted with a postgre tag. — Dipen, Jun 08 '15 at 03:13
Isn't that the same question as: http://stackoverflow.com/questions/30694777/sorting-rows-before-joining-one-table-to-multiple-tables (and it's postgres, not postgre) — , Jun 08 '15 at 05:47

score 0 · Accepted Answer · edited May 23 '17 at 12:14

SELECT 
* 
FROM question 
INNER JOIN answer on question.answers_id = answer.answer_id
WHERE question_id = 1

This the basic query that you need. After that you have to use crosstab, but since you have unlimited number of answer rows its very difficult. The method in detailed on this SO post Dynamic alternative to pivot with CASE and GROUP BY.

Another method is to use array_agg function, but the result wont be the same as the you need

SELECT 
    question_id, array_agg(question_title) AS 'questionlist', array_agg(answers_id || '##' || answer_text || '##' || is_top_answer) AS 'answerlist'
FROM question 
INNER JOIN answer on question.answers_id = answer.answer_id
WHERE question_id = 1  
GROUP BY question_id

After that you have to split the questionlist and answerlist in the server side code

Combining a variable amount of rows into one row?

1 Answers1