Geek Answers Handbook

Subclass table query

There are currently 3 tables in my application database:

  • Parent table - (common goal)
  • childâ
  • Childb

If I spoke from the point of view of OOP, then ChildA and ChildB are “subclasses” of the parent table, but they are not similar.

Relations between tables:

  • The row in the parent table has an integer that determines whether the row is associated with type A (ChildA) or type B (ChildB).
  • ChildA and ChildB have a link to the related row in the parent table (id). There can be only 1 parent row associated with the child, and there can also be 1 child associated with the parent (one-to-one r / s).
  • Inside all tables, there are no pairs of columns with the same name.

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

This would be very easy to do if I first got all the parent rows and then skipped the rows with the loop and query ntimes for each row, but that would probably be very inefficient, I think.

I was wondering if there is a better approach to this, perhaps even in one request.

I know that I can use INNER JOINor something like that, but I'm not sure how it works in this case, when I need to join two tables with a third (and where the columns differ in both number and content) .

So the question is, what would be the most effective way to transform it?

EDIT:
, , , , . Table-Per-Type ( 2).
, , , , , ( , , JOIN). , , , .

+4
3

( :)

1) , , . :

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')

. " " , ( )

2) , ChildA, ChildB :

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

, ( , ChildA 5, ChildB 5). , "" :

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)

, ChildA ChildB NULL ( ChildA ChildB NULL). , ( , ), .

+3

:

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

100% , ,

, . , 1, 2. .

- , , . , .

0

, .

, , 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;
Hide result

, .

0
source

More articles:


All Articles