CODEWITHSUNDEEP
mysql
andy
andy

Reputation: 2399

Multiple columns in one subquery

I have a bit of a problem. I have a database layout like so:

customer
customer_id, name, age, etc...

customer_survey_question
id, category, caption, type

customer_survey_answer
id, customer_id, customer_survey_question_id, answer

and I need to pull in their answers like so:

name, age, etc..., question 1, question 2, question 3, etc...

Now I could do a sub-query:

SELECT
  `customer`.*,
  (
    SELECT `answer`
    FROM `customer_survey_answer`
    WHERE `customer_survey_answer`.`customer_id`=`customer`.`customer_id`
    AND `id`=1
  ) AS `question_1`,
  (
    SELECT `answer`
    FROM `customer_survey_answer`
    WHERE `customer_survey_answer`.`customer_id`=`customer`.`customer_id`
    AND `id`=2
  ) AS `question_2`,
  ....

But there is 14 questions and I need to be able to do this pretty quickly and expand up to 80+ questions. What is the best way to approach?

Upvotes: 0

Views: 83

Answers (4)

Carsten Massmann
Carsten Massmann

Reputation: 28196

If you want to generate a table in a single query you could use the group by on a query with a single INNER JOIN and then collect the values in several MAX( CASE ... END ) expressions. Sounds complicated at first but it does work reliably (just add another line for each desired column):

SELECT name, age,
MAX(CASE WHEN customer_survey_question_id=1 THEN answer END) a1,
MAX(CASE WHEN customer_survey_question_id=2 THEN answer END) a2,
MAX(CASE WHEN customer_survey_question_id=3 THEN answer END) a3,
MAX(CASE WHEN customer_survey_question_id=4 THEN answer END) a4,
MAX(CASE WHEN customer_survey_question_id=5 THEN answer END) a5,
MAX(CASE WHEN customer_survey_question_id=6 THEN answer END) a6,
MAX(CASE WHEN customer_survey_question_id=7 THEN answer END) a7,
MAX(CASE WHEN customer_survey_question_id=8 THEN answer END) a8,
...
FROM customer c INNER JOIN customer_survey_answer s 
                ON s.customer_id=c.customer_id

Upvotes: 0

Jiri Fornous
Jiri Fornous

Reputation: 410

It is not wise to force DB output into result's columns.

In case you would like to operate over one only customer, make simple question query and then answer query to get everything and put it together outside of DB - in PHP for example.

In case you would like the list of customers with their answers, make first the question query and then make the "answer x customer" query with respective ids and put them together using hashing outside the DB. It works well and fast unless you don't use paging for your output list ;).

Hash can work like this

// suppose we have from database
// $answer_list(id,customer_id,customer_name,question_id,answer)
// $question_list(id,question)

// hash 
$customer_list = array();
$customer_hash = array();
foreach ($answer_list as $answer)
{
  $customer_id = $answer['customer_id'];
  if (!isset($customer_hash[$customer_id]))
  {
    $customer_list[] = $customer_id;
    $customer_hash[$customer_id]['name'] = $answer['customer_name'];
  }
  $customer_hash[$customer_id]['answer_hash'][$answer['question_id']] = $answer;
}


// output
foreach ($customer_list as $customer_id)
{
  echo $customer_hash[$customer_id]['id'];
  echo $customer_hash[$customer_id]['name'];
  foreach ($question_list as $question)
  {
    echo $question['question'];
    echo $customer_hash[$customer_id]['answer_hash'][$question['id]]['answer'];
  }
}

Upvotes: 0

Steven
Steven

Reputation: 6148

You cannot do this through a query by and of itself.

Look here for some example code: How to merge data from 2 tables with MySQL

What you can do is use an INNER JOIN or GROUP_CONCAT and then reformat the data in your script (whether that be php or another language)

Using JOIN

Unser the circumstances this would likely lead to a large excess/irrelevant data in each row

SELECT c.*, csa.answer
FROM customers c
INNER JOIN
    customer_survey_answer csa ON csa.`customer_id`=c.`customer_id`
ORDER BY c.customer_id, csa.customer_survey_question_id

Using GROUP_CONCAT

This will output one cell (csv style) as the answers

SELECT c.*, GROUP_CONCAT(csa.answer) as answers
FROM customers c
INNER JOIN
    customer_survey_answer csa ON csa.`customer_id`=c.`customer_id`
GROUP BY c.customer_id

Using a loop

You might also consider querying the database for a list of customers with answers and then running a second query (for each customer returned) to get their answer. This could lead to a large number of queries.

First query:

SELECT * FROM customers

Second query:

SELECT answer
FROM customer_survey_answer
WHERE customer_id = INSERT_CUSTOMER_ID_HERE}

Upvotes: 1

exussum
exussum

Reputation: 18550

you want to pivot the data

SELECT class,GROUP_CONCAT(member)
FROM tbl
GROUP BY class;

is the basic case

http://www.artfulsoftware.com/infotree/queries.php#78

Upvotes: 0

Related Questions