Re: 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