How to query parent & child table in one query?

Question

First, I'll address concerns about duplicates:

How to query a parent table and inherited child table together in one query
- This question is similar but it doesn't provide a concrete example
How can you represent inheritance in a database? suggests "Class Table Inheritance", which is the pattern I'm using, but does not explain how to query it effectively.

Here's a example of the problem I'm facing:

table Document {
    id: Id
    name: string
    type: ??
}

table FooDoc {
   id: Id
   // Foreign key to Document
   docId: Id
   qux: string
}

table BarDoc {
   id: Id
   // Foreign key to document
   docId: Id
   baz: number
}

Ideally, I'd like to make it so that in 1 query, I can

grab a document based on its id
grab the relevant data from the correct child table

Is this possible?

Yes, a couple of left joins will get the data you want. I would suggest you define the `id` columns (one per table) to establish the PKs and FKs correctly, first. Then the query is trivial. — The Impaler, Apr 17 '22 at 03:44
@TheImpaler, I added the primary keys and the foreign keys to the schema. Looking at left joins, it seems like it returns all records in the left set, which is not what I want -- given that'd I only want to return 1 row (the doc with the correct id in the `Document` table plus the correct metadata in the `BarDoc` table)? — Foobar, Apr 17 '22 at 04:29
which RDBMS are you using? MySQL,SQL Server,postgresql,..... . tag it correctly — RF1991, Apr 17 '22 at 04:46
Use a CTE to UNION the child tables and then do an INNER JOIN between your Document table and the CTE. Ideally you would repeat any WHERE requirements in the queries making up the UNION. — Jonathan Willcock, Apr 17 '22 at 04:49

score 0 · Answer 1 · answered Apr 17 '22 at 13:13

There are six ways (afaik) to model table inheritance in relational databases. You chose the Permissive Class Table Inheritance option.

Now, you can use two left joins to retrieve information for child tables. The resulting columns from the non-matching type will be null.

For example:

select d.*, f.qux, b.baz
from document d
left join foodoc f on f.id = d.id
left join bardoc b on b.id = d.id

Result:

 id  name  type  qux      baz  
 --- ----- ----- -------- ---- 
 20  baz1  2     null     1240 
 10  foo1  1     content  null

See running example at DB Fiddle. As you can see, column qux is null for type 2 and column baz is null for type 1.

The sample structure for this example is shown below:

create table document (
  id int primary key not null, 
  name varchar(10), 
  type int not null check (type in (1, 2))
);

insert into document (id, name, type) values
  (10, 'foo1', 1),
  (20, 'baz1', 2);

create table foodoc (
  id int primary key not null references document(id),
  qux varchar(10)
);

insert into foodoc (id, qux) values (1, 'content');

create table bardoc (
  id int primary key not null references document(id),
  baz int
);

insert into bardoc (id, baz) values (2, 1240);

Note: Also please consider that to fully implement integrity you would need to include the type column in both foreign keys.

How to query parent & child table in one query?

1 Answers1