CODEWITHSUNDEEP
mysqlsql
user1032531
user1032531

Reputation: 26311

SQL joining supertype and subtype tables

I have a supertype table along with two child subtype tables, and a third normal table. My question relates to joining the normal table to one of the subtype tables without including the intermediate supertypeTable. As seen by the CREATE statement below, my indexes for normalTable1 all relate to supertypeTable and not either of the subtype tables, however, the subtype tables have the same PK as the supertype table. Could I simply do SELECT whatever FROM subtypeTable1 AS s INNER JOIN normalTable1 AS n ON n.supertypeTable_id=s.supertypeTable_id..., or do I need to include additional indexes or also include supertypeTable in the join? Thank you

supertypeTable
-id (PK)
-data

subtypeTable1
-supertypeTable_id (PK and also FK with a 1-to-1 relationship to supertypeTable.id)
-data

subtypeTable2
-supertypeTable_id (PK and also FK with a 1-to-1 relationship to supertypeTable.id)
-data

normalTable1
-id (PK)
-supertypeTable_id (FK with a many-to-one relationship to supertypeTable.id)
-data

  CREATE normalTable1 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  supertypeTable_id INT UNSIGNED NOT NULL ,
  data 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;

Upvotes: 0

Views: 3166

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95731

As long as the keys match, you don't need to include unneeded intermediate tables in your query. You can just join your normal table to a subtype table. SQL (and relational) joins are based on matching values, not on following a path.

But your table structure has some problems unrelated to this question. See this SO answer, and look closely at the "party_type" column in it.

Upvotes: 1

Related Questions