Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a one table Q&A like below:

CREATE TABLE  `totaalconceptterreinonderhoud`.`acties` 
(
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
      `type` int(11) NOT NULL DEFAULT '3' COMMENT 'Actie types:\n1 = vraag\n2 = antwoord\n3 = afspraak\n4 = nieuws\n',
      `aanvang_actie` date NOT NULL COMMENT 'Aanvang afspraak datum + tijd',
      `start_tijd` time NOT NULL COMMENT 'Start tijd',
      `eind_tijd` time NOT NULL COMMENT 'Eind tijd',
      `proj_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Koppeling met Project',
      `onderwerp` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Onderwerp afspraak',
      `notitie` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Tekstuele omschrijving',
      `locatie` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
      `meld_aan_contacts` int(11) NOT NULL DEFAULT '1' COMMENT 'Melden aan contactpersonen',
      `koppel_id` int(11) NOT NULL DEFAULT '0' COMMENT 'koppelinng met andere id',
      `aangemaakt_door` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Wie heeft laatst aangepast',
      `aanmaak_datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Wanneer aangemaakt',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Acties'

There are 4 types of record:

  • type=1 (question)
  • type=2 (answer)
  • 3 and 4 (not relevant now)

What I want is to list all questions with their answers below. The questions and answers have a koppel_id field that connects them. The ordering must be last asked question first, with answers right below.

Is this hard to do? Any help would be nice.

share|improve this question
    
Can you let us know what is the date column you want to sort by (my ?Dutch? is not good)? Also, let us know if assumptions can be made about correlation between id and the date? –  Unreason Oct 18 '11 at 17:02

3 Answers 3

It would be something like the following. Please verify the syntax and functionality as I have not tested it. There might be a better way to do this.

SELECT * FROM
(
    SELECT
        @rownum1 := @rownum1 + 1 AS rownum,
        acties.*
    FROM
        acties
        INNER JOIN (SELECT @rownum1 := 0) AS rownum
    WHERE
        type = 1
    ORDER BY
        aanmaak_datum DESC
) AS question
UNION
SELECT * FROM
(
    SELECT question2.rownum, answers.*
    FROM acties AS answers
    INNER JOIN
    (
        SELECT
            @rownum2 := @rownum2 + 1 AS rownum,
            acties.*
        FROM
            acties
            INNER JOIN (SELECT @rownum2 := 0) AS rownum2
        WHERE
            type = 1
        ORDER BY
            aanmaak_datum DESC
    ) AS question2 ON answers.koppel_id = question2.koppel_id
    WHERE answers.type = 2
) as answer2
ORDER BY rownum, type
share|improve this answer
    
From the looks of your query, it appears you are actually numbering them and presenting question and answers in an orderly fashion. +1 !!! –  RolandoMySQLDBA Oct 18 '11 at 22:24
SELECT * FROM 
(
    SELECT Q.id,'Question' QAType,Q.notitie,K1.aanmaak_datum,K1.koppel_id,Q.type
    FROM
    (
        SELECT koppel_id,type,aanmaak_datum
        FROM acties
        WHERE type=1
        ORDER BY aanmaak_datum DESC
    ) K1
    INNER JOIN acties Q
    USING (koppel_id,type)
    UNION
    SELECT A.id,'Answer' QAType,A.notitie,K2.aanmaak_datum,K2.koppel_id,A.type
    FROM
    (
        SELECT koppel_id,type,id,aanmaak_datum
        FROM acties
        WHERE type=1
        ORDER BY aanmaak_datum DESC
    ) K2
    INNER JOIN acties A
    ON K2.koppel_id=A.koppel_id AND A.type=2
) QA
ORDER BY koppel_id,aanmaak_datum desc,type
;

You will also need decent indexes to help with subqueries:

ALTER TABLE acties ADD INDEX koppel_id_type_ndx (koppel_id,type);

ALTER TABLE acties ADD INDEX aanmaak_datum_koppel_id_type_ndx (aanmaak_datum,koppel_id,type);

The reason for the index : All the fields being read and used will be retrieved via full index scan (preordered) rather than full table scan and temporary sorting.

Give it a Try !!!

share|improve this answer
    
OP says: 'The ordering must be last asked question first, with answers right below.' I am a bit lost with with Dutch(?), but I don't think your query returns records in that order. –  Unreason Oct 18 '11 at 15:55

Are the "answers" the official answers to the questions, or are they answers given by the people being tested?

Either way, I would separate the questions and answers into their own tables.

You could have a list of questions in one table, a list of all possible answers in another table. You would have a relational table in between that would have a QuestionID/AnswerID record for every valid combination.

Storing a student's answer to these questions is a separate matter entirely, and should not be mixed into these two tables.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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