Ordering by date and other columns

Question

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.

Answer 1

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

Answer 2

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 !!!

Answer 3

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.