Postgresql – Postgres relationship statement

postgresqlrelations

Lets say I have the table parents. A parent has many kids and a kid has many toys What would be the best way to do a SELECT statement for the parent and grab all of kids associated with that parent and all the toys associated the that kid?

Best Answer

To start to answer this question, I created 4 tables (see the end of the post for sample data - or better still, see the fiddle here):

CREATE TABLE parent (parent_id INTEGER, parent_name VARCHAR(20));
CREATE TABLE child (child_id INTEGER, parent_id INTEGER, child_name VARCHAR(20));
CREATE TABLE toy (toy_id INTEGER, toy_name VARCHAR(40));
CREATE TABLE child_toy (child_id INTEGER, toy_id INTEGER);

This last table is a "joining table" which maintains an m-n connection between two others (in this case, child and toy). There are many childen and many toys and children can have more than one toy.

I leave it as an exercise to put in the necessary FOREIGN KEY constraints. You can also use Oracle SEQUENCEs rather than explicitly inputting ids.

Now, this query will produce all toys for all children for all parents.

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.child_id
  INNER JOIN toy t
  ON ct.toy_id = t.toy_id;

Result:

parent_name;child_name;toy_name
Fred;Jimmy;Barbie
Fred;Jimmy;Monopoly
..
<lines deleted>
..
Joe;Helen;Soccer ball
Joe;Helen;Rugby ball

You can play around with the fiddle (here) and experiment with it to develop your SQL skills.

  • Find all children who don't have a toy.
  • Find all parents none of whose children has a toy.
  • Find the child who has the most toys.
  • Find the parent whose children have the most toys.
  • Find all children who only have 1 toy (more than 1, less than 3... &c.).

You may need to input further sample data for this.

Here's a small hint:

SELECT 
  p.parent_name, 
  c.child_name,
  COUNT(t.toy_id)
FROM parent p
  INNER JOIN child c
  ON p.parent_id = c.parent_id
  INNER JOIN child_toy ct 
  ON c.child_id = ct.child_id
  INNER JOIN toy t
  ON ct.toy_id = t.toy_id
GROUP BY p.parent_name, c.child_name
HAVING (COUNT(t.toy_id) > 3);

parent_name;child_name;count
Fred;Jimmy;4

So, child Jimmy with parent Fred has more than 3 toys. There endless combinations which you can try out! Enjoy!

============== Sample Data =============

parent:

INSERT INTO parent VALUES (1, 'Fred');
INSERT INTO parent VALUES (2, 'Mary');
INSERT INTO parent VALUES (3, 'Joe');

child:

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');

toy:

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');

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);