Mysql Complex Union

I have the following MySQL query working so far: SELECT CONCAT('program:', program_pk) AS global_id, program_name AS name, NULL AS parent_global_id FROM program UNION

Solution 1:

If your desired hierarchy is program -> theme -> strand -> year -> unit -> learning_event, then you should adjust your table structure to reflect this. In particular theme should have a foreign key relationship between theme and program, and you need an extra level for year. Having a foreign key between theme and program avoids the need for a cross join. Cross joins have a habit of biting you and are generally to be avoided.

If you look at this db fiddle you will see that I have made these changes. I have called the year level strandyear to avoid using a reserved word, but the intention should be clear. Now the joins become inner joins (instead of left joins) to pick up the description values from higher levels in the tree, and the bottom level (learning_events) automatically only contains values that match strand, year, and unit, for no other reason than that the structure itself guarantees it, through the simple expedient that each level has a foreign key to the level above.

Note that the foreign keys effectively chain link. You do not need, for example, a specific foreign key between learning_event and strand, because the intervening keys in the chain guarantee the relationship.

Solution 2:

Assuming that program -> theme -> strand -> year -> unit -> learning event means that

• A program has 1 or more themes
• A theme has 1 or more strands etc

Then you need

CREATETABLE program (program_id ...)
CREATETABLE theme   (theme_id ...,  program_id, ...)
CREATETABLE strand  (strand_id ..., theme_id, ...)
etc

This implements a 1:many relationship between each pair consecutive pair of tables.

A typical one would, in more detail:

CREATETABLE theme (
    theme_id INT UNSIGNED AUTO_INCREMENT NOTNULL,
    theme_name VARCHAR(99) NOTNULL,
    program_id INT UNSIGNED NOTNULL,   -- link to the program this theme is inPRIMARY KEY(theme_id)
) ENGINE=InnoDB

(Optionally you could add a FOREIGN KEY constraint to further emphasize the comment I included.)