4

In the database of my app there are currently 3 tables:

  • Parent Table - (general goal)
  • ChildA
  • ChildB

If I were to speak in terms of OOP, both ChildA and ChildB are "subclasses" of the Parent table, however they are not similar.

The Relationships between the 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).
  • In both ChildA and ChildB there is a reference to the related row in Parent Table (id). There can be only 1 Parent row related to a child and there can also be 1 child related to a parent (one-to-one r/s).
  • There is not any pair of columns with the same name inside all of the tables.

What I'm trying to do is to basically retrieve all of the rows in the Parent table, then according to the type column of each row to retrieve additional related info from either ChildA or ChildB.

This would be very easy to do if I were to first retrieve all of the parent rows, and then run through the rows with a loop and query n times for every row, but that would probably be highly inefficient, I guess.

I was wondering whether there is a better approach to this, perhaps even in a single query.

I know I could use INNER JOIN or something, but I'm not sure how it'd work in this case where I need to join 2 tables with a third one (and where the columns are different both in number and content).

So the question is, what would be the most efficient way to preform it?

EDIT:
I saw this question was marked as a duplicate of another question, however, I do not ask how to design my database, but how to QUERY it.
I'm using a Table-Per-Type design, and would like to get all of the rows from all of the different types (currently 2).
I would know how to do so in a case where I wanted to get all of the rows from a single type, but not in this situation, which is why I'm asking whether and how it would be possible with a single query (with a mechanism similar to JOIN for example). I know I could achieve it by querying twice, but I'd like to learn a more efficient way to do it.

Community
  • 1
  • 1
Asaf
  • 2,005
  • 7
  • 37
  • 59

3 Answers3

3

I can think of two different approaches (with their pluses and minuses :)

1) Have as many queries as subtypes and retrieve a subtype at a time. In the example case, 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 give you the lowest possible data transfer between your application and the database at a relatively reasonable performance. You will "waste" the effort your database makes to filter the Parent table (the database will have to do it twice)

2) You have one query which retrieves both ChildA and ChildB as part of the same result set like this:

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 only works if children have unique ids (that is, if there is a ChildA with id 5, there is no ChildB with id 5). If this is not the case, you need a slightly "uglier" 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 which contains all columns from both ChildA and ChildB with many NULL values (for each ChildA record, all ChildB columns would be NULL). This way, you have a single query (which may perform faster that the multiple queries in the first approach), but you need to ship more data.

xpa1492
  • 1,953
  • 1
  • 10
  • 19
0

You can create a join for this like this:

select * from (a outer join b on a.key = b.fg_key) outer join c on a.key = c.fg_key

I am not 100% sure about the placement of the opening brace but I remember using this before

But as the amount of sub-types grows, it will become more and more complex to maintain this properly, all column names must be aliased in the query. It would be easiest to perform a two step load, first load all items of type 1, then of type 2. This will keep the code cleaner and easier to maintain.

Efficiency-wise I don't expect this to be much slower than a single query. I would suggest going with the multiple query variant in the first version and optimize when performance becomes an issue.

nickmartens1980
  • 1,593
  • 13
  • 23
0

Basically this kind of requirement we should try to handle in our programme we should not try to achieve from database.

However I tried below query where we it outputs from one of the child table and in other child only null;

select parent.id, parent.description, (select name from car where id = parent.id) as child1, (select Name from bike where id = parent.id) as child2 from vehicle parent;

Hope this will help you.

techi sp
  • 1