CODEWITHSUNDEEP
sqltreeextractrecorddescendant
PNS
PNS

Reputation: 19895

Reading an SQL record and all descendants

What would be the most efficient way to selectively read records from a SQL table, along with all their descendants - i.e., records in other tables with different schemas but of the same database, referencing the starting table via foreign keys?

For example, the following 4 tables have a primary key called id and records in Table A are referenced from the other 3 tables via the column x_id, where x = B, C, or D and x_id is the foreign key pointing to a record of Table X with primary key id.

Table A: id
Table B: id, a_id
Table C: id, a_id, b_id
Table D: id, a_id, c_id

Assuming that somehow the application knows the "graph" of table foreign key dependencies and thus can enumerate all "children" of a record (i.e., all records from other tables directly referencing it via its id), the data can be retrieved via multiple SELECT queries. That, however, becomes very slow for large tables.

Is there any more efficient way of getting the same information?

Upvotes: 0

Views: 60

Answers (0)

Related Questions