mysql - SQL joining supertype and subtype tables -

mysql - SQL joining supertype and subtype tables -

i have supertype table along 2 kid subtype tables, , 3rd normal table. question relates joining normal table 1 of subtype tables without including intermediate supertypetable. seen create statement below, indexes normaltable1 relate supertypetable , not either of subtype tables, however, subtype tables have same pk supertype table. select whatever subtypetable1 s inner bring together normaltable1 n on n.supertypetable_id=s.supertypetable_id..., or need include additional indexes or include supertypetable in join? give thanks you

supertypetable -id (pk) -data subtypetable1 -supertypetable_id (pk , fk 1-to-1 relationship supertypetable.id) -data subtypetable2 -supertypetable_id (pk , fk 1-to-1 relationship supertypetable.id) -data normaltable1 -id (pk) -supertypetable_id (fk many-to-one relationship supertypetable.id) -data create normaltable1 ( id int unsigned not null auto_increment , supertypetable_id int unsigned not null , info varchar(45) null , primary key (id) , index fk1 (supertypetable_id asc) , constraint fk2 foreign key (supertypetable_id ) references supertypetabl (id ) on delete no action on update no action, engine = innodb;

as long keys match, don't need include unneeded intermediate tables in query. can bring together normal table subtype table. sql (and relational) joins based on matching values, not on next path.

but table construction has problems unrelated question. see this answer, , closely @ "party_type" column in it.

mysql sql