SQLize | PHPize | SQLtest

CREATE TABLE events ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL ); CREATE TABLE my_promos ( id INT AUTO_INCREMENT PRIMARY KEY, event_id INT NOT NULL, user_id INT NOT NULL, link_code VARCHAR(50) NOT NULL, date DATE NOT NULL, FOREIGN KEY (event_id) REFERENCES events(id) ); CREATE TABLE tickets_sold ( id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(50) NOT NULL, quantity INT NOT NULL ); INSERT INTO events (name) VALUES ('Concert A'), ('Festival B'), ('Gala C'); INSERT INTO my_promos (event_id, user_id, link_code, date) VALUES (1, 532, 'ABC123', '2013-11-05'), (1, 532, 'ABC123', '2013-12-01'), (2, 532, 'XYZ789', '2013-12-15'), (3, 999, 'OTHER1','2013-12-20'); INSERT INTO tickets_sold (code, quantity) VALUES ('ABC123', 10), ('ABC123', 5), ('XYZ789', 20), ('OTHER1', 3); SELECT e.id, COALESCE(SUM(ts.quantity),0) sold_tickets FROM events e JOIN my_promos mp ON e.id = mp.event_id JOIN tickets_sold ts ON mp.link_code = ts.code WHERE mp.user_id = 532 AND mp.date >= '2013-11-01' AND mp.date <= '2014-01-22' GROUP BY e.id;