-- Collection of code snippets by Arne Vajhøj 
-- (likely posted to comp.os.vms/INFO-VAX, INFO-TPU, MACRO32, eksperten.dk, newz.dk, LinkedIn or other place sometime between 1990 and now) 
CREATE TABLE topics (
    topic_id INTEGER NOT NULL,
    PRIMARY KEY (topic_id)
);

CREATE TABLE topics_track (
    user_id INTEGER NOT NULL,
    topic_id INTEGER NOT NULL,
    mark_time VARCHAR(50),
    PRIMARY KEY (user_id,topic_id)
);

INSERT INTO topics VALUES(1);
INSERT INTO topics VALUES(2);
INSERT INTO topics VALUES(3);

INSERT INTO topics_track VALUES(52, 1, 'Mandag');
INSERT INTO topics_track VALUES(52, 2, 'Tirsdag');
INSERT INTO topics_track VALUES(53, 1, 'Onsdag');
INSERT INTO topics_track VALUES(53, 2, 'Torsdag');

SELECT * FROM topics;
SELECT * FROM topics_track;

SELECT t.topic_id, tt.mark_time
FROM topics t JOIN topics_track tt ON t.topic_id=tt.topic_id
ORDER BY t.topic_id;

SELECT t.topic_id, tt.mark_time
FROM topics t JOIN topics_track tt ON t.topic_id=tt.topic_id
WHERE tt.user_id=53
ORDER BY t.topic_id;

SELECT t.topic_id, tt.mark_time
FROM topics t LEFT JOIN topics_track tt ON t.topic_id=tt.topic_id
ORDER BY t.topic_id;

SELECT t.topic_id, tt.mark_time
FROM topics t LEFT JOIN topics_track tt ON t.topic_id=tt.topic_id
WHERE tt.user_id=53 ORDER BY t.topic_id;

SELECT t.topic_id, tt.mark_time
FROM topics t LEFT JOIN topics_track tt ON t.topic_id=tt.topic_id
WHERE (tt.user_id=53 OR tt.user_id IS NULL)
ORDER BY t.topic_id;

DROP TABLE topics;
DROP TABLE topics_track;
