-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TABLE vare (
    vareid INTEGER PRIMARY KEY,
    navn VARCHAR(50),
    antal INTEGER
);

CREATE TABLE salg (
    salgid INTEGER PRIMARY KEY,
    vareid INTEGER,
    antal INTEGER
);

INSERT INTO vare VALUES (1, 'A', 10);
INSERT INTO vare VALUES (2, 'B', 10);
INSERT INTO vare VALUES (3, 'C', 10);

INSERT INTO salg VALUES (1, 1, 2);
INSERT INTO salg VALUES (2, 1, 2);
INSERT INTO salg VALUES (3, 2, 2);

SELECT vare.vareid,vare.navn,vare.antal-IFNULL(x.totalsalg, 0) AS antaltilbage
FROM vare LEFT JOIN (SELECT vareid,SUM(antal) AS totalsalg FROM salg GROUP BY vareid) x ON vare.vareid=x.vareid;

DROP TABLE vare;
DROP TABLE salg;
