Java – use relational query tables similar to subclasses

There are currently three tables in the database of my application:

>Parent table – (total goal) > childa > childb

If I want to speak with OOP, childa and childb are "subclasses" of the parent table, but they are not similar

Relationship between tables:

>A row in the parent table has an integer that defines whether the row is related to type A (childa) or type B (childb). > both childa and childb have references to the related row in the parent table (ID). There is only one parent row related to the child, and there can also be one child item related to the parent (one-to-one R / s). > there are no column pairs with the same name in all tables

What I want to do is basically retrieve all rows in the parent table, and then retrieve other relevant information from childa or childb according to the type column of each row

If I first retrieve all the parent rows and then iterate over the rows using a loop and query n times per row, it will be very easy, but I think it may be very inefficient

I wonder if there is a better way, maybe even in a query

I know I can use inner join or something else, but I'm not sure how it works in this case. I need to connect the second table to the second table (and the number and content of columns are different)

So the question is, what is the most effective way to prefabricate it?

Editor: I see that this question is marked as a copy of another question, but I don't ask how to design my database, but how to query it. I'm using table per type design and want to get all rows of all different types (currently 2). When I want to get all rows from a single type, I'll know how to do this, I know I can implement it by querying twice, but I want to learn a more effective way to implement it

resolvent:

I can think of two different methods (with their advantages and disadvantages:)

1) Have as many queries as subtypes and retrieve subtypes at once. In the example, you will have two queries:

select * from ChildA where id in (select childId from Parent where childType='A')
select * from ChildB where id in (select childId from Parent where childType='B')

This will provide you with the lowest possible data transfer between the application and the database with relatively reasonable performance. You will "waste" the work done by the database to filter the parent table (the database must be executed twice)

2) You have a query that retrieves childa and childb as part of the same result set, as follows:

select ChildA.*, ChildB.* from Parent
    left outer join ChildA on Parent.ChildId=ChildA.id
    left outer join ChildB on Parent.ChildId=ChildB.id

The above query is only valid if the child has a unique ID (that is, if there is childa with ID 5, there is no childb with ID 5). If this is not the case, you need a slightly "ugly" query:

select ChildA.*, ChildB.* from Parent, ChildA, ChildB
    where (Parent.ChildType='A' and Parent.ChildId=ChildA.id) or
          (Parent.ChildType='B' and Parent.ChildId=ChildB.id)

This will give you a result set that contains all columns from childa and childb with many null values (all childb columns will be null for each childa record). In this way, you have only one query (in the first method, it may execute faster than multiple queries), but you need to send more data