clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591569 fiddles created (45738 in the last week).

select version();
version
PostgreSQL 9.5.24 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
 hidden batch(es)


CREATE TABLE parent ( parent_id INTEGER PRIMARY KEY, parent_name VARCHAR(20 ));
 hidden batch(es)


CREATE TABLE child ( child_id INTEGER PRIMARY KEY, parent_id INTEGER, child_name VARCHAR(20), CONSTRAINT child_parent_fk FOREIGN KEY (parent_id) REFERENCES parent (parent_id) );
 hidden batch(es)


CREATE TABLE toy ( toy_id INTEGER PRIMARY KEY, toy_name VARCHAR(40) );
 hidden batch(es)


CREATE TABLE child_toy ( ct_child_id INTEGER, ct_toy_id INTEGER, CONSTRAINT ct_child_fk FOREIGN KEY (ct_child_id) REFERENCES child (child_id), CONSTRAINT ct_toy_fk FOREIGN KEY (ct_toy_id) REFERENCES toy (toy_id) );
 hidden batch(es)


INSERT INTO parent VALUES (1, 'Fred'); INSERT INTO parent VALUES (2, 'Mary'); INSERT INTO parent VALUES (3, 'Joe');
1 rows affected
1 rows affected
1 rows affected
 hidden batch(es)


INSERT INTO child VALUES (1, 1, 'Jimmy'); INSERT INTO child VALUES (2, 1, 'Gerry'); INSERT INTO child VALUES (3, 1, 'Billy'); INSERT INTO child VALUES (4, 2, 'Mick'); INSERT INTO child VALUES (5, 2, 'James'); INSERT INTO child VALUES (6, 2, 'Larry'); INSERT INTO child VALUES (7, 3, 'Jim'); INSERT INTO child VALUES (8, 3, 'Jack'); INSERT INTO child VALUES (9, 3, 'Helen');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
 hidden batch(es)


INSERT INTO toy VALUES (1, 'Action Man'); INSERT INTO toy VALUES (2, 'Barbie'); INSERT INTO toy VALUES (3, 'Monopoly'); INSERT INTO toy VALUES (4, 'Cluedo'); INSERT INTO toy VALUES (5, 'Hurley'); INSERT INTO toy VALUES (6, 'Soccer ball'); INSERT INTO toy VALUES (7, 'Rugby ball'); INSERT INTO toy VALUES (8, 'Tennis ball'); INSERT INTO toy VALUES (9, 'Tennis raquet'); INSERT INTO toy VALUES (10, 'Tricycle');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
 hidden batch(es)


-- child_toy: INSERT INTO child_toy VALUES(1, 2); INSERT INTO child_toy VALUES(1, 3); INSERT INTO child_toy VALUES(1, 4); INSERT INTO child_toy VALUES(1, 5); INSERT INTO child_toy VALUES(2, 3); INSERT INTO child_toy VALUES(2, 9); INSERT INTO child_toy VALUES(3, 1); INSERT INTO child_toy VALUES(3, 2); INSERT INTO child_toy VALUES(4, 6); INSERT INTO child_toy VALUES(5, 7); INSERT INTO child_toy VALUES(6, 9); INSERT INTO child_toy VALUES(7, 3); INSERT INTO child_toy VALUES(8, 4); INSERT INTO child_toy VALUES(9, 5); INSERT INTO child_toy VALUES(9, 6); INSERT INTO child_toy VALUES(9, 7);
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
 hidden batch(es)


SELECT p.parent_name, c.child_name, t.toy_name FROM parent p INNER JOIN child c ON p.parent_id = c.parent_id INNER JOIN child_toy ct ON c.child_id = ct.ct_child_id INNER JOIN toy t ON ct.ct_toy_id = t.toy_id;
parent_name child_name toy_name
Fred Jimmy Barbie
Fred Jimmy Monopoly
Fred Jimmy Cluedo
Fred Jimmy Hurley
Fred Gerry Monopoly
Fred Gerry Tennis raquet
Fred Billy Action Man
Fred Billy Barbie
Mary Mick Soccer ball
Mary James Rugby ball
Mary Larry Tennis raquet
Joe Jim Monopoly
Joe Jack Cluedo
Joe Helen Hurley
Joe Helen Soccer ball
Joe Helen Rugby ball
 hidden batch(es)


SELECT p.parent_name, c.child_name, COUNT(t.toy_id) AS cnt FROM parent p INNER JOIN child c ON p.parent_id = c.parent_id INNER JOIN child_toy ct ON c.child_id = ct.ct_child_id INNER JOIN toy t ON ct.ct_toy_id = t.toy_id GROUP BY p.parent_name, c.child_name ORDER BY cnt DESC;
parent_name child_name cnt
Fred Jimmy 4
Joe Helen 3
Fred Gerry 2
Fred Billy 2
Joe Jack 1
Mary Larry 1
Mary Mick 1
Mary James 1
Joe Jim 1
 hidden batch(es)


SELECT p.parent_name, c.child_name, COUNT(t.toy_id) cnt FROM parent p INNER JOIN child c ON p.parent_id = c.parent_id INNER JOIN child_toy ct ON c.child_id = ct.ct_child_id INNER JOIN toy t ON ct.ct_toy_id = t.toy_id GROUP BY p.parent_name, c.child_name HAVING COUNT(t.toy_id) > 3 ORDER BY cnt DESC;
parent_name child_name cnt
Fred Jimmy 4
 hidden batch(es)