Conditional JOINs in Class Table Inheritance in mySQL

Question

Is there a way to do a conditional JOIN so that it returns columns of either of one of the child tables based on a parent table field?

---

tl;dr:

I am trying to implement Class Table Inheritance (aka Table Per Type Inheritance) in mySQL. AFAIK it's not offered out of the box so some hacking around is needed to make it tick.

Considering the following SQL schema.

What I'd like to do:

• Get records of CustomerIndividual IF they exist OR CustomerOrganization IF they exist.
• I'm passing around idCustomer for my WHERE clauses.

---

An Ideal Result Set:

• If Customer.typeIndividual = TRUE:

• If Customer.typeOrganization = TRUE:

It's important to note that this query will always return 1 record only, so yes the column number would be variable.

---

What I'm currently doing:

  SELECT *
  FROM Customer
  LEFT JOIN CustomerIndividual
  ON Customer.idCustomer = CustomerIndividual.idCustomer
  LEFT JOIN CustomerOrganization
  ON Customer.idCustomer = CustomerOrganization.idCustomer
  WHERE Customer.idCustomer = ?

but this of course:

• returns ALL columns for Customer, CustomerIndividual & CustomerOrganization
• returns even duplicate columns, e.g idCustomer which appears on all 3 tables.
• produces columns of CustomerOrganization even if the Customer is an Individual and vice-versa.

My question:

Is there a way to do a conditional JOIN so that it returns columns of either of one of the child tables based on the Customer.typeIndividual/Customer.typeOrganization fields?

Notes:

• Either Customer.typeIndividual OR Customer.typeOrganization can be TRUE at any given time. Not both at the same time. The Customer can be either an Individual OR an Organization.

• I'm open to restructuring if it makes things more simple. I'm having the feeling that the Customer.typeIndividual OR Customer.typeOrganization differentiation flag I use is somehow redundant, i.e perhaps I could infer whether a Customer is an Individual/Organization by looking which of the child-tables is full.

• I'm aware I could make completely different queries on the application-level but yes, I'm looking for a silver-bullet.

Answer 1

Maybe you can do this, but I dont recommend it.

SELECT
  Customer.*,
  COALESCE(CI.firstName, CO.organizationName) AS firstName_or_organizationName,
  COALESCE(CI.maidenName, CO.registrationNo) AS maidenNamee_or_registrationNo,
  lastName --  here you get last_name or NULL if is an organization
  FROM Customer C
  LEFT JOIN CustomerIndividual  CI
         ON Customer.idCustomer = CustomerIndividual.idCustomer
  LEFT JOIN CustomerOrganization CO
         ON Customer.idCustomer = CustomerOrganization.idCustomer
  WHERE Customer.idCustomer = ?

Or use a store procedure

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name(IN CustomerID INT)
BEGIN
    DECLARE boolTypeIndividual INT;

    SELECT Customer.typeIndividual into boolTypeIndividual
    FROM Customer
    WHERE Customer.idCustomer = CustomerID 

    IF (boolTypeIndividual) THEN
            SELECT C.*,   CI.*
            FROM Customer C
            LEFT JOIN CustomerIndividual  CI
                   ON Customer.idCustomer = CustomerIndividual.idCustomer
            WHERE Customer.idCustomer = CustomerID 
    ELSE
            SELECT C.*,   CO.*
            FROM Customer C
            LEFT JOIN CustomerOrganization CO
                   ON Customer.idCustomer = CustomerOrganization.idCustomer
            WHERE Customer.idCustomer = CustomerID 
    END IF
END;;
DELIMITER ;