Thread: [NOVICE] How to get n records from parent table and their children

Hi,

Am 15.11.17 um 20:14 schrieb JORGE MALDONADO:
> I have a parent and child tables and need to get "n" records from
> parent table and all of the records in child for such "n" records in
> parent.
Do you have tables like this?

utz=> \d parent                       Table "pg_temp_3.parent"Column |  Type   |                      Modifiers
--------+---------+-----------------------------------------------------id     | integer | not null default
nextval('parent_id_seq'::regclass)name  | text    |
 
Indexes:   "parent_pkey" PRIMARY KEY, btree (id)
Referenced by:   TABLE "child" CONSTRAINT "child_parent_id_fkey" FOREIGN KEY
(parent_id) REFERENCES parent(id)

lutz=> \d child                        Table "pg_temp_3.child" Column   |  Type   |                     Modifiers
-----------+---------+----------------------------------------------------id        | integer | not null default
nextval('child_id_seq'::regclass)name     | text    |parent_id | integer |
 
Indexes:   "child_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:   "child_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES parent(id)

lutz=> select * from parent;id | name
----+------ 1 | foo 2 | bar 3 | baz
(3 rows)

lutz=> select * from child;id |    name     | parent_id
----+-------------+----------- 1 | foo-child-1 |         1 2 | foo-child-2 |         1 3 | bar-child-1 |         2 4 |
baz-child-1|         3
 
(4 rows)

If you now want to select all "child" rows that have parent in a limited
set, you can use a subquery
(https://www.postgresql.org/docs/current/static/functions-subquery.html):

lutz=> select * from child where parent_id in ( select id from parent limit 2
);id |    name     | parent_id
----+-------------+----------- 1 | foo-child-1 |         1 2 | foo-child-2 |         1 3 | bar-child-1 |         2
(3 rows)

Lutz


-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

JORGE MALDONADO wrote:
> I have a parent and child tables and need to get "n" records from parent table
> and all of the records in child for such "n" records in parent. What can be a good approach?

I'd try it as follows:

SELECT ...
FROM (SELECT p_id, ...     FROM parent     LIMIT 42) p  JOIN child c     USING (p_id);

This assumes that "p_id" is the key column in "parent"
and the foreign key column in "child".

Yours,
Laurenz Albe


-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice