Trying to build a table pulling from parent and child data

Hi all, SQL newbie here trying to solve a problem where I have a mental block.

My database catalogues miniatures. All miniatures belong to sets. Some of the sets are “sub-sets” of other sets. When I display a gallery of miniatures, I want them to display as:

Show Set Name
Show Gallery of Set Miniatures
Show Sub-Set Name
Show Gallery of Sub-Set Miniatures

My Sets table contains all the info on Sets, and includes subsets, but makes no distinction whether the details recorded are a set or a subset.

My SubSets table just creates a parent/child relationship between the parent set id# and the child set id#.

My Miniatures table has a column for the sets id#.

I am having trouble trying to figure out how to have a single query build a table with both the set results and the child set results. (Later I will order these, but right now I just can’t figure out how to get both results).

The user will be selecting a set they wish to view the gallery of which will correspond to a specific set_id #.

Here’s what I have so far:

    $sql="SELECT tableMinis.*, tableImages.imag_name, tableRarity.rare_name, tableSizes.size_name tableSubSets.subs_sets_id_child FROM tableMinis 
        LEFT JOIN tableImages ON tableMinis.mini_imag_id = tableImages.imag_id
        LEFT JOIN tableRarity ON tableMinis.mini_rarity = tableRarity.rare_id
        LEFT JOIN tableSizes ON tableMinis.mini_size = tableSizes.size_id
        LEFT JOIN tableSubSets ON tableMinis.mini_sets_id = tableSubSets.subs_sets_id_parent
        WHERE tableMinis.mini_sets_id = ? OR tableMinis.mini_sets_id = tableSubSets.subs_sets_id_child

I know the “tableMinis.mini_sets_id = tableSubSets.subs_sets_id_child” is wrong, as it will just match anytime there is an association in the table, and not just the association I am looking for.

I am trying to achieve a clause that basically states:

  • Get all results from tableMinis where the mini_sets_id is equal to the user-selection,
  • AND also get all results from tableMinis where the mini_id matches the parent/child relationship of mini_sets_id as per the user-selection.

Is there a simple way of achieving this?

This is no different than Categories. This tutorial will show you how to handle Hierarchical Data.

Take a look at The Adjacency List Model on the page.

here’s my article on the adjacency list :sunglasses:

caution, links may not work

Thanks guys, but I’m really beyond the limits of my intelligence when it comes to managing hierarchical data. I sort of understand the flow, but I am unable to apply it.

I really don’t understand foreign keys, when to use them or why to use them. They are references to other tables, but you can already make references to other tables without using foreign key – very confused.

Looking through the examples though, I think maybe I was going about it backwards by trying to link a parent to child. It looks like I should be linking a child to parent.

I have revised my query as such:

SELECT tableMinis.*, tableSubSets.subs_sets_id_parent FROM tableMinis 
        LEFT JOIN tableSubSets ON tableMinis.mini_sets_id = tableSubSets.subs_sets_id
        WHERE tableMinis.mini_sets_id = ? OR tableSubSets.subs_sets_id_parent = ?

I have changed the 2 columns in my tableSubSets to subs_sets_id (which is the id of sets and/or children), and subs_sets_id_parent (which is the id the parent in the Sets table).

I think this should work, but am a little ways away from having the tables populated and having the code together to run it.

Out of curiousity, does it make sense that I have a separate table where I am assigning a parent to a set (which is not done very often), or should I just have a column in the sets table for recording the parent id (most of which would be null values)?

On a side note, I think I have finally reached comprehension of when to use foreign key.

If I am understanding correctly, this links a column to primary key column in another table, and should that primary key number change, then the link id in the other table would be automatically changed to match it.

Mmmh no. That’s a cascading value.
A Foreign Key should never change. The idea of a key is that it uniquely identifies the row, and so you should never change it in that direction.

The first half of your explanation is correct and complete. A FK in table B identifies a/some field(s) in table B that uniquely identifies a row in table A.

A car has a single owner (simplification, but roll with it). There are two tables: people, and cars. The cars table would contain a FK field, ‘owner’, that links a car to a person.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.