-- 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 main (
    id INTEGER NOT NULL,
    txt VARCHAR(50) NOT NULL,
    PRIMARY KEY(id)
);
CREATE TABLE subs (
    id INTEGER NOT NULL,
    nam VARCHAR(50) NOT NULL,
    main_id INTEGER NOT NULL,
    PRIMARY KEY(id)
);

INSERT INTO main VALUES(1, 'All A');
INSERT INTO main VALUES(2, 'All B');
INSERT INTO main VALUES(3, 'All C');
INSERT INTO subs VALUES(1, 'A1.jpg', 1);
INSERT INTO subs VALUES(2, 'B1.jpg', 2);
INSERT INTO subs VALUES(3, 'B2.jpg', 2);
INSERT INTO subs VALUES(4, 'B3.jpg', 2);

SELECT main.txt,subs.nam
FROM main JOIN subs ON main.id=subs.main_id
ORDER BY main.txt;

SELECT main.txt,subs.nam
FROM main LEFT JOIN subs ON main.id=subs.main_id
ORDER BY main.txt;

SELECT main.txt,GROUP_CONCAT(subs.nam)
FROM main LEFT JOIN subs ON main.id=subs.main_id
GROUP BY main.txt
ORDER BY main.txt;

DROP TABLE subs;
DROP TABLE main;
