1

I have two table question and answer. The structure is given below

CREATE TABLE IF NOT EXISTS `question` (
`id` int(10) NOT NULL,
  `question` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `question` (`id`, `question`) VALUES
(1, 'Who is Mammotty'),
(2, 'Who Rahul Ghandi');

CREATE TABLE IF NOT EXISTS `answers` (
`id` int(10) NOT NULL,
  `q_id` int(10) NOT NULL,
  `answer` varchar(100) NOT NULL,
  `status` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

INSERT INTO `answers` (`id`, `q_id`, `answer`, `status`) VALUES
(1, 1, 'Actor', 'Y'),
(2, 1, 'Farmer', 'N'),
(3, 1, 'Engineer', 'N'),
(4, 1, 'Driver', 'N'),
(5, 2, 'Politician', 'Y'),
(6, 2, 'Teacher', 'N'),
(7, 2, 'Doctor', 'N'),
(8, 2, 'Pilot', 'N');

I want to display question and answer in this format

question         | ans1       | status1 | ans2   | status2 | ans3     | status3 | ans4   | status3
---------------------------------------------------------------------------------------------------
Who is Mammotty  | Actor      | Y       | Farmer | N       | Engineer | N       | Driver | N
Who Rahul Ghandi | Politician | Y       | Teacher| N       | Doctor   | N       | Pilot  | N

Can any one Help me ?

2
  • 1
    Do you know how many question/answer combinations you are going to have?
    – Taryn
    May 11, 2015 at 15:52
  • 1
    Dynamic, It contains N numbers of questions and each questions contain four answers. May 11, 2015 at 16:40

2 Answers 2

0

If each questions having exactly 4 answers the below query will help you. Same way you can include the status field.

SELECT qst.question, SUBSTRING_INDEX( group_concat( ans.answer ) , ',', 1 ) AS Ans1, SUBSTRING_INDEX( SUBSTRING_INDEX( group_concat( ans.answer ) , ',', 2 ) , ',', -1 ) AS Ans2, SUBSTRING_INDEX( SUBSTRING_INDEX( group_concat( ans.answer ) , ',', -2 ) , ',', 1 ) AS Ans3, SUBSTRING_INDEX( group_concat( ans.answer ) , ',', -1 ) AS Ans4 FROM question qst, answers ans WHERE ans.q_id = qst.id GROUP BY ans.q_id

0

you can use the below query to get the result. i think this will work for you

SELECT qs.question,group_concat(an.answer) as myanswer FROM answers an,question qs WHERE qs.id=an.q_id group by an.q_id

1
  • Hi Vasanthan, Thanks for your response. Unfortunately this query will return all the answers with comma separated format. Not the format what I am currently looking for. May 12, 2015 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.