Please Solve this query - mysql

Question

i have below table where i m storing question and multiple answers with id of quetion as below

table
ID Content QID
1  Q1      0
2  Q2      0
3  A1      1
4  A1      2
5  A2      1
6  A2      2

in above example Q for Question and A for Answer

Now i need result as question then its answer and so on e.g

I need output like First question and then all answer of that question.. then second question and all answers of that question...

1 Q1
3 A1
5 A2
2 Q2
4 A1
6 A2

I don't really understand what do you need. If you need to random order the rows, see this : http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql — Vucko, Nov 29 '12 at 08:22
I need output like First question and then all answer of that question.. then second question and all answers of that question... — samirprogrammer, Nov 29 '12 at 08:26
@Kumar: The question is not that clear. Plus he's not explaining it with a lot of words... — Frederik Prijck, Nov 29 '12 at 08:44

vstm · Accepted Answer · 2012-11-29T08:51:49.263

1

Here's one cheap solution (tested with MySQL):

SET @max_id = (SELECT MAX(ID) FROM `table`);
SELECT * FROM `table` ORDER BY IF(QID > 0, @max_id * QID + ID, @max_id * ID)

It works only for one level of Questions/Answers. So if an answer can have sub-questions and sub-answers, this solution won't work for you. I'm sure there are better solutions.

If you want to run it in one query, you can do it like that:

SELECT * FROM `table`
ORDER BY IF(QID > 0,
  (SELECT MAX(ID) FROM `table`) * QID + ID,
  (SELECT MAX(ID) FROM `table`) * ID)

edited Nov 29 '12 at 08:51

answered Nov 29 '12 at 08:35

vstm

12,407
1
51
47

Thanks.. but when i test it is showing as Q1,Q2,A1,A2.. i need question then all answer and then next question and all answer of it – samirprogrammer Nov 29 '12 at 08:43
Maybe it's because of the @max_id variable. I have now added a second query, could you try that? – vstm Nov 29 '12 at 08:52
Yes but still same answer is in proper order but both question coming first..e.g. Q1,Q2... – samirprogrammer Nov 29 '12 at 08:57
Well I've tested it with your sample data and it worked. So I guess you're doing it with your real data. You probably have to modify the expression `QID > 0` - if your questions have a QID of 1 (in your sample they have 0) you have to write `QID > 1`. – vstm Nov 29 '12 at 09:02
Thanks a lot it is wroking... – samirprogrammer Nov 29 '12 at 09:08

score 1 · Answer 2 · answered Nov 29 '12 at 08:40

First of all, I realy think this is a bad way of storing your data. Second, I even think it is a bad way of retrieving your data...

Anyway, I did not find a SQL statement for your result yet, but maybe this can help you:

SELECT question.ID as QuestionID, question.Content as Question, 
answer.ID as AnswerID, answer.Content as Answer 
FROM Test question, Test answer 
WHERE question.ID = answer.QID 
ORDER BY question.Content ASC

This should allow you to do what you want with the results... (Tested with SQL Server)

Please Solve this query - mysql

2 Answers2