What do item recipes, monster evolutions, and character abilities have in common? These are all examples of video game mechanics that are best represented by “one-to-many relations” in a relational database (in MediaWiki, this means Cargo).¹

In this article, we’ll go over what the phrase “one-to-many relation” means, how to store such data in Cargo, and how to query it. We’ll also briefly discuss what I call the “Cargo attach trick” and “escaped CONCAT” formatting.

What is a relation?

A relation is a set of facts about an entity. For example, “Paris is a city in France” or “Paris is a city in France, and it is a capital city, and its population is 2 million.” We could represent this in the following table:

Relations in video games

Here are some common entities in video games along with what properties you might care about for them:

And here’s some example data for a hypothetical “Items” entity:

{| class="wikitable"
! Item !! Element !! Weight
|-
| Sunshine Elixir || Fire || 5
|-
| Reflective Cloak || Air || 10
|}

In this case, the two relations depicted are:

• Sunshine Elixir is an item with element Fire and weight 5
• Reflective Cloak is an item with element Air and weight 10

Multipart data fields

In the table above, each property has one single value for each instance of the entity. For example, each character class only has one base HP, and each weapon only has one range. But what about the ingredients you need to craft an item, or the different evolutions of a boss, or a list of spells available to a character class? Here are some examples:

In these cases, the columns Ingredients, Spells, and Evolutions are plural - each row has multiple values here, or in other words for every one relation (row of data), there are many values in this column.

{| class="wikitable"
! Item !! Element !! Weight !! Ingredients
|-
| Sunshine Elixir || Fire || 5 || Sunstone (x3), Orange Juice (x2)
|-
| Reflective Cloak || Air || 10 || Cloth (x10), Mystic Feather (x5)
|}

{| class="wikitable"
! Monster !! Faction !! Evolutions
|-
| Invisible Boy || Air Nomads || Translucent Boy, Invisible Boy
|-
| Dragon || Fire Nation || Baby Dragon, Dragon, Elder Dragon
|}

{| class="wikitable"
! Character class !! Base HP !! Base Mana !! Spells
|-
| Sorcerer || 30 || 60 || Chain Lightning, Fireball, Scry, Magic Missile
|-
| Druid || 60 || 45 || Shapeshift: Lion, Shapeshift: Tiger, Shapeshift: Bear
|-
| Fighter || 100 || 15 || Dash, Bash, Smash
|}

Alternative representations of multipart data fields

Version 1

Let’s redraw these tables a bit:

I’ve added a few fields in this version, but hopefully you can see that this is the same data, just visually rearranged a bit.

Version 2

Let’s focus on the items-and-ingredients data and visually rearrange a bit more. Our goal here is to not have any rowspans.

<div style="display:flex; gap:1em;">
<div>
{| class="wikitable"
! colspan = 3 | Item data
|-
! Item !! Element !! Weight
|-
| Sunshine Elixir || Fire || 5
|-
| Reflective Cloak || Air || 10
|}</div><div>
{| class="wikitable"
! colspan = 3 | Ingredient data
|-
! Item !! Ingredient !! Quantity
|-
| Sunshine Elixir || Sunstone || 3
|-
| Sunshine Elixir || Orange Juice || 2
|-
| Reflective Cloak || Cloth || 10
|-
| Reflective Cloak || Mystic Feather || 5
|}</div>
</div>

Instead of using “rowspan” to put multiple (“many”) ingredients in each item row, we’ve migrated the ingredient data outside of the item table and created a separate table of just the ingredient info. The same data is being represented here, but with some advantages over the previous representations:

• We never have a case of putting multiple data points into a single cell (like Sunstone (x3), Orange Juice (x2))
• We don’t have cases of multiple columns having rowspans (this would be hard to represent in a database); in the “Ingredient data” table, we have just the “Item name” column that might be repeated, which is the minimum amount of repeated data necessary to be able to correctly reproduce the previous view.
  • If we omitted the column Item from the Ingredient data table, we wouldn’t know if Sunstone is an ingredient for Sunshine Elixir or for Reflective Cloak or for Mystic Ink
  • We don’t need any additional data from Item data to be present in the Ingredient data for the reconstruction to be possible, because an item name uniquely identifies a row in the Item data table

Try it yourself

Can you create this type of visualization for monster evolutions and for character class spells? You might find this template code useful:

<div style="display:flex; gap:1em;">
<div>
{| class="wikitable"
|}</div><div>
{| class="wikitable"
|}</div>
</div>

<div style="display:flex; gap:1em;">
<div>
{| class="wikitable"
! Monster !! Faction
|-
| Invisible Boy || Air Nomads
|-
| Dragon || Fire Nation
|}</div><div>
{| class="wikitable"
! colspan = 4 | Evolution data
|-
! Monster name !! Evolution !! HP !! Attack
|-
| Invisible Boy || Translucent Boy || 30 || 15
|-
| Invisible Boy || Invisible Boy || 45 || 20
|-
| Dragon || Baby Dragon || 50 || 20
|-
| Dragon || Dragon || 150 || 40
|-
| Dragon || Elder Dragon || 450 || 80
|}</div>
</div>

<div style="display:flex; gap:1em;">
<div>
{| class="wikitable"
! colspan = 3 | Class data
|-
! Character class !! Base HP !! Base Mana
|-
| Sorcerer || 30 || 60 
|-
| Druid || 60 || 45 
|-
| Fighter || 100 || 15 
|}</div><div>
{| class="wikitable"
! colspan = 3 | Spell data
|-
! Class !! Spell name !! Mana cost
|-
| Sorcerer || Chain Lightning || 30
|-
| Sorcerer || Fireball || 30
|-
| Sorcerer || Scry || 5
|-
| Sorcerer || Magic Missile || 10
|-
| Druid || Shapeshift: Lion || 20
|-
| Druid || Shapeshift: Tiger || 20
|-
| Druid || Shapeshift: Bear || 20
|-
| Fighter || Dash || 15
|-
| Fighter || Bash || 15
|-
| Fighter || Smash || 15
|}</div>
</div>

Declaring Cargo data

Incorrect option - list-type fields

I am not a fan of list-type fields. If you were to try to represent your data using a list field, here is what you might try:

{{#cargo_declare:_table=Items
|Name=String
|Element=String <!-- you could maybe put allowed values here, but I wouldn't -->
|Weight=Integer <!-- or Float if there can be fractional weights -->
|Ingredients=List (,) of String
|Quantities=List (,) of Integer
}}

In this case, the data for our items table will look like this:

{| class="wikitable"
! Item !! Element !! Weight !! Ingredients !! Ingredient Quantities
|-
| Sunshine Elixir || Fire || 5 || Sunstone, Orange Juice || 3, 2
|-
| Reflective Cloak || Air || 10 || Cloth, Mystic Feather || 10, 5
|}

What happens if we want to query all items that use a Mystic Feather in their ingredients list, along with the quantity of Mystic Feathers? In this representation, Mystic Feather doesn’t really know how to access 5

Incorrect option: Many numbered columns

What if we tried to use just 1 table and enumerate each ingredient + associated info in a single table? That could look like this:

{{#cargo_declare:_table=Items
|Name=String
|Element=String
|Weight=Integer
|Ingredient1=String
|Quantity1=Integer
|Ingredient2=String
|Quantity2=Integer
|Ingredient3=String
|Quantity3=Integer
}}

Now our data looks like this:

{| class="wikitable"
! Item !! Element !! Weight !! Ingredient 1 !! Quantity 1 !! Ingredient 2 !! Quantity 2 !! Ingredient 3 !! Quantity 3
|-
| Sunshine Elixir || Fire || 5 || Sunstone || 3 || Orange Juice || 2 || ||
|-
| Reflective Cloak || Air || 10 || Cloth || 10 || Mystic Feather  || 5 || ||
|}

This is probably the most common representation that I’ve seen people use on wikis for situations like this, and it’s not fantastic. Here are a couple problems:

• It’s easy to hit the column limit on tables. What happens if there are up to 7 ingredients and each one has a quantity, a minimum quality required, whether it’s mandatory or optional, and (for optional ingredients) what bonus attribute it contributes to the item on its inclusion? Then we have Ingredient, Quantity, MinQuality, IsMandatory,² and Bonus (times 7) - taking up 35 of our available columns! If items have two sets of such “one-to-many” data, we will quickly arrive at our column limit.
• It’s a very fragile system; what happens if suddenly an item is patched to require an 8th ingredient? What if some items have multiple recipes? In either case, it’s quite difficult to make the needed updates to the schema to accommodate such a situation.
• It makes many queries a lot more difficult to express. How do we know which slot Mystic Feather belongs in? If we want to find all items that have a Mystic Feather as one of its ingredients, we’ll have to write out a huge OR statement - and if the maximum recipe size ever increases, we’ll have to edit every single such query on the entire wiki to include the new column(s).
• What happens if we want to compute the total weight of all ingredients used to make an item? We’d want to join another copy of Items on Items.Name=Items.Ingredient1…but wait, what about the other ingredients? We’d need to add SEVEN different extra copies of Items to our join. Yikes!

Correct solution - separate tables

In fact, we have already seen the correct solution! Recall this depiction of our data:

Let’s make two separate Cargo tables, one called Items and one called Ingredients:

<!-- At Template:Items/CargoDeclare -->
{{#cargo_declare:_table=Items
|Name=String
|Element=String
|Weight=Integer
}}

<!-- At Template:Ingredients/CargoDeclare -->
{{#cargo_declare:_table=Ingredients
|Ingredient=String
|Product=String <!-- the item that the ingredient builds into -->
|Quantity=Integer
}}

Now, if we want to query all items that include Mystic Feather, we can do it like so:

{{#cargo_query:table=Ingredients
|where=Ingredient="Mystic Feather"
|fields=Product
}}

Which will look like this:³

(Recall that by default Cargo displays data as a comma-separated list when you request one field; and as a table when you request multiple fields.)

Soon, we’ll see how to query data about both the ingredients and the item at the same time, using a “JOIN” statement (|join on= in Cargo).

Try it yourself

Can you write out Cargo declarations for the monster & character class examples?

<!-- At Template:Monsters -->
{{#cargo_declare:_table=Monsters/CargoDeclare
|Name=String
|Faction=String
}}

<!-- At Template:MonsterEvolutions/CargoDeclare -->
{{#cargo_declare:_table=MonsterEvolutions
|EvolutionName=String
|MonsterName=String
|HP=Integer
|Attack=Integer
}}

<!-- At Template:CharacterClasses/CargoDeclare -->
{{#cargo_declare:_table=CharacterClasses
|Name=String
|HpBase=Integer
|ManaBase=Integer
}}

<!-- At Template:Spells/CargoDeclare -->
{{#cargo_declare:_table=Spells
|Name=String
|CharacterClass=String
|ManaCost=Integer
}}

The Cargo attach trick

One small issue with splitting your data into multiple tables is that Cargo only lets you declare or attach one table per template. What if you want to attach multiple tables at the same time??

It turns out that the important part about attaching isn’t that you attach to the template doing the storing, but that you attach to any template that’s present every time you store.

For example, the following are equivalent (assume the templates do what they say they do):

{{MyTemplateThatStoresAndAttaches}}

{{MyTemplateThatStoresButDoesntAttach}}{{MyTemplateThatAttaches}}

If you split out the attach from Template:MyTemplateThatStoresButDoesntAttach to Template:MyTemplateThatAttaches and include the latter every time you use the former, you’re good!

But wait, there’s more!

What if we call Template:MyTemplateThatAttaches from within MyTemplateThatStoresButDoesntAttach? Then we can just write:

{{MyTemplateThatStoresButDoesntAttach}}

And Template:MyTemplateThatAttaches is automatically transcluded every time!

Here’s a concrete example:

<includeonly>{{#cargo_store:_table=Items
|Name={{{name|}}}
|Element={{{element|}}}
|Weight={{{weight|}}}
}}{{#if:{{{ingredient1|}}}|{{#cargo_store:_table=Ingredients
|Item={{{name|}}}
|Ingredient={{{ingredient1|}}}
|Quantity={{{quantity1|}}}
}}{{IngredientsCargoAttach}}<!-- /if -->}}</includeonly><noinclude>{{#cargo_attach:Items}}{{documentation}}</noinclude>

Try it yourself

Modify the example above to support storing up to 8 ingredients.

<includeonly>{{#cargo_store:_table=Items
|Name={{{name|}}}
|Element={{{element|}}}
|Weight={{{weight|}}}
}}{{#if:{{{ingredient1|}}}|{{#cargo_store:_table=Ingredients
|Item={{{name|}}}
|Ingredient={{{ingredient1|}}}
|Quantity={{{quantity1|}}}
}}{{IngredientsCargoAttach}}<!-- we only need to attach once 
-->{{#if:{{{ingredient2|}}}|{{#cargo_store:_table=Ingredients
|Item={{{name|}}}
|Ingredient={{{ingredient2|}}}
|Quantity={{{quantity2|}}}
}}{{#if:{{{ingredient3|}}}|{{#cargo_store:_table=Ingredients
|Item={{{name|}}}
|Ingredient={{{ingredient3|}}}
|Quantity={{{quantity3|}}}
}}{{#if:{{{ingredient4|}}}|{{#cargo_store:_table=Ingredients
|Item={{{name|}}}
|Ingredient={{{ingredient4|}}}
|Quantity={{{quantity4|}}}
}}{{#if:{{{ingredient5|}}}|{{#cargo_store:_table=Ingredients
|Item={{{name|}}}
|Ingredient={{{ingredient5|}}}
|Quantity={{{quantity5|}}}
}}{{#if:{{{ingredient6|}}}|{{#cargo_store:_table=Ingredients
|Item={{{name|}}}
|Ingredient={{{ingredient6|}}}
|Quantity={{{quantity6|}}}
}}{{#if:{{{ingredient7|}}}|{{#cargo_store:_table=Ingredients
|Item={{{name|}}}
|Ingredient={{{ingredient7|}}}
|Quantity={{{quantity7|}}}
}}<!-- /if7 -->}}<!-- /if6 -->}}<!-- /if5 -->}}<!-- /if4 -->}}<!-- /if3 -->}}<!-- /if2 -->}}<!-- /if1 -->}}</includeonly><noinclude>{{#cargo_attach:Items}}{{documentation}}</noinclude>

See also

You may not encounter this issue when dealing with one-to-many data, indeed generally you would want to use multiple templates. See that article for some additional suggestions surrounding storing one-to-many data.

Writing your first JOIN statement

The first step is to join our two tables together. Joining in SQL means more or less the same thing as what the word “join” means in English - to put two (or more!) things together. We’ll look at the items + ingredients data first, so the things we want to join are the Items table and the Ingredients table.

In our first example, we’re going to get the weight and element of every craftable item that uses a Mystic Feather by querying the Ingredients table and joining it to the Items table.

In Cargo, we can write it like this:⁴

|tables=Ingredients, Items
|join on=Ingredients.Product=Items.Name

Here is an example query and its result:⁵

{{#cargo_query:tables=Ingredients, Items
|join on=Ingredients.Product=Items.Name
|where=Ingredient="Mystic Feather"<!-- here is where we use data from Ingredients -->
|fields=Ingredients.Product,Items.Element,Items.Weight <!-- the corresponding data from Items -->
}}

Querying with GROUP_CONCAT

In the example above, we got data about one ingredient, Mystic Feather. But what if we want data about all ingredients & items at once?

Example 1 - all item recipes

We’re going to try and make something like this:

Here’s what we’re going to query:

• From the Items table:
  • A list of all items
  • The element
  • The weight
• From the Ingredients table, connecting the product name to the item name via join on:
  • The ingredient name
  • The ingredient quantity

Here’s version 1 of the query:

{{#cargo_query:tables=Items,Ingredients
|join on=Items.Name=Ingredients.Product
|fields=Items.Name,Items.Element,Items.Weight,GROUP_CONCAT(Ingredients.Ingredient)=Ingredients, GROUP_CONCAT(Ingredients.Quantity)=Quantity
|group by=Ingredients.Product
|limit=5
}}

But we want the Ingredients and Quantity together:

{{#cargo_query:tables=Items,Ingredients
|join on=Items.Name=Ingredients.Product
|fields=Items.Name,Items.Element,Items.Weight,
  GROUP_CONCAT(Ingredients.Ingredient, ": ", Ingredients.Quantity)
  =Ingredients
|group by=Ingredients.Product
|limit=5
}}

Fantastic! We’ll talk about how you can format this in a bit.

Example 2 - all items using an ingredient

In this example, we’ll display all items by what they’re used to make. First, we’ll get the data, no formatting:

{{#cargo_query:tables=Ingredients, Items
|join on=Ingredients.Product=Items.Name
|fields=Ingredients.Ingredient, GROUP_CONCAT(Ingredients.Product)=Product, GROUP_CONCAT(Items.Element)=Element
|group by=Ingredients.Ingredient
}}

And now we’ll format some of the columns together:

{{#cargo_query:tables=Ingredients, Items
|join on=Ingredients.Product=Items.Name
|fields=Ingredients.Ingredient,
    GROUP_CONCAT(Ingredients.Product, " (", Items.Element, ")")=Product
|group by=Ingredients.Ingredient
}}

Escaped-CONCAT formatting

Finally, let’s look at how we can customize our queries to look a bit prettier.

Dependencies

Your wiki must have the following templates:

• Template:(( - outputs the characters {{
• Template:)) - outputs the characters }}

Example 1

{{#cargo_query:tables=Items,Ingredients
|join on=Items.Name=Ingredients.Product
|fields=Items.Name,Items.Element,Items.Weight,
  GROUP_CONCAT(Ingredients.Ingredient, ": ", Ingredients.Quantity)
  =Ingredients
|group by=Ingredients.Product
|limit=5
}}

Instead of writing Mystic Feather: 5, we want to write the following:

[[File:mystic feather.png|link=Mystic Feather|20x20px]] [[Mystic Feather]] (5x)

That’ll look something like this:

Let’s start out by expressing this as a literal string using CONCAT to split up the pieces:

GROUP_CONCAT(
    "[[File:", <!-- literal string -->
    "mystic feather", <!-- comes from Ingredients.Ingredient -->
    ".png|link=", <!-- literal string -->
    "Mystic Feather", <!-- comes from Ingredients.Ingredient -->
    "|20x20px]] [[", <!-- literal string --> 
    "Mystic Feather", <!-- comes from Ingredients.Ingredient -->
    "]] (", <!-- literal string -->
    5, <!-- comes from Ingredients.Quantity -->
    "x)" <!-- literal string -->
)

And now let’s substitute in the field names and query:

{{#cargo_query:tables=Items,Ingredients
|join on=Items.Name=Ingredients.Product
|fields=Items.Name,Items.Element,Items.Weight, GROUP_CONCAT(
    "[[File:", <!-- literal string -->
    Ingredients.Ingredient,
    ".png|link=", <!-- literal string -->
    Ingredients.Ingredient,
    "|20x20px]] [[", <!-- literal string --> 
    Ingredients.Ingredient,
    "]] (", <!-- literal string -->
    Ingredients.Quantity,
    "x)" <!-- literal string -->
)=Ingredients
|group by=Ingredients.Product
|limit=5
}}

But oh no!!!! Everything is broken because our file name needs to be lowercased!! How can we fix this?

How to fix this

What we really want is {{lc:Ingredients.Ingredient}}, not Ingredients.Ingredient. Here’s a couple ways we could try writing this, and neither works:

GROUP_CONCAT(
    "[[File:", <!-- literal string -->
    {{lc:Ingredients.Ingredient}},
    ".png|link=", <!-- literal string -->
    Ingredients.Ingredient,
    "|20x20px]] [[", <!-- literal string --> 
    Ingredients.Ingredient,
    "]] (", <!-- literal string -->
    Ingredients.Quantity,
    "x)" <!-- literal string -->
)

GROUP_CONCAT(
    "[[File:{{lc:", <!-- literal string -->
    Ingredients.Ingredient,
    "}}.png|link=", <!-- literal string -->
    Ingredients.Ingredient,
    "|20x20px]] [[", <!-- literal string --> 
    Ingredients.Ingredient,
    "]] (", <!-- literal string -->
    Ingredients.Quantity,
    "x)" <!-- literal string -->
)

The problem with those attempts, is that the {{lc:}} function will resolve before the evaluated result is sent to Cargo. We need to have {{lc:}} evaluate afterwards. The key lies in the templates Template:(( and Template)). These are transcluded by typing {{((}} and {{))}}, respectively. (Yes, it can look a bit confusing.) Let’s try it:

<!-- snip -->
        "[[File:{{((}}lc:", <!-- note the invocation of Template:(( -->
        Ingredients.Ingredient,
        "{{))}}.png|link=", <!-- note the invocation of Template:)) -->
<!-- snip -->

{{#cargo_query:tables=Items,Ingredients
|join on=Items.Name=Ingredients.Product
|fields=Items.Name,Items.Element,Items.Weight, GROUP_CONCAT(
    "[[File:{{((}}lc:", <!-- literal string, using Template:(( -->
    Ingredients.Ingredient,
    "{{))}}.png|link=", <!-- literal string, using Template:)) -->
    Ingredients.Ingredient,
    "|20x20px]] [[", <!-- literal string --> 
    Ingredients.Ingredient,
    "]] (", <!-- literal string -->
    Ingredients.Quantity,
    "x)" <!-- literal string -->
)=Ingredients
|group by=Ingredients.Product
|limit=5
}}

And this works!

Making a list (ul) inside the GROUP_CONCAT

We can set the SEPARATOR to \n, or a newline, and prefix each item with *:

|fields=Items.Name,Items.Element,Items.Weight, GROUP_CONCAT(
    "* [[File:{{((}}lc:", <!-- literal string -->
<!-- snip -->
    ")x" <!-- literal string -->
SEPARATOR "\n" <!-- the separator is for the GROUP_CONCAT, so it's outside the CONCAT -->
)=Ingredients

{{#cargo_query:tables=Items,Ingredients
|join on=Items.Name=Ingredients.Product
|fields=Items.Name,Items.Element,Items.Weight, GROUP_CONCAT(
    "* [[File:{{((}}lc:", <!-- literal string -->
    Ingredients.Ingredient,
    "{{))}}.png|link=", <!-- literal string -->
    Ingredients.Ingredient,
    "|20x20px]] [[", <!-- literal string --> 
    Ingredients.Ingredient,
    "]] (", <!-- literal string -->
    Ingredients.Quantity,
    ")x" <!-- literal string -->
SEPARATOR "\n" <!-- the separator is for the GROUP_CONCAT, so it's outside the CONCAT -->
)=Ingredients
|group by=Ingredients.Product
|limit=5
}}

Example 2

{{#cargo_query:tables=Ingredients, Items
|join on=Ingredients.Product=Items.Name
|fields=Ingredients.Ingredient,
    GROUP_CONCAT(Ingredients.Product, " (", Items.Element, ")")=Product
|group by=Ingredients.Ingredient
}}

This time, we’re going to write a custom template, called ItemWithElement, which will look like this:

[[File:{{{Element|}}}.png|20x20px|link=]] [[{{{Item|}}}]]

For example, here is how {{ItemWithElement|Item=Flameheart Pendant|Element=Fire}} looks:

The principle to display this in our Cargo query is similar what we did above for {{lc:}}:

GROUP_CONCAT(
    "{{((}}ItemWithElement{{!}}Item=", <!-- Using Template:(( and the magic word {{!}} for {{ and | -->
    Ingredients.Product,
    "{{!}}Element=", <!-- Using the magic word {{!}} for a literal | character -->
    Items.Element,
    "{{))}}" <!-- remember to close the template -->
)=Products

And here’s the full query:

{{#cargo_query:tables=Ingredients, Items
|join on=Ingredients.Product=Items.Name
|fields=Ingredients.Ingredient,
    GROUP_CONCAT(
        "{{((}}ItemWithElement{{!}}Item=", <!-- Using Template:(( and the magic word {{!}} for {{ and | -->
        Ingredients.Product,
        "{{!}}Element=", <!-- Using the magic word {{!}} for a literal | character -->
        Items.Element,
        "{{))}}" <!-- remember to close the template -->
    )=Products
|group by=Ingredients.Ingredient
}}

Or as a list:

{{#cargo_query:tables=Ingredients, Items
|join on=Ingredients.Product=Items.Name
|fields=Ingredients.Ingredient,
    GROUP_CONCAT(
        "* {{((}}ItemWithElement{{!}}Item=", <!-- Using Template:(( and the magic word {{!}} for {{ and | -->
        Ingredients.Product,
        "{{!}}Element=", <!-- Using the magic word {{!}} for a literal | character -->
        Items.Element,
        "{{))}}" <!-- remember to close the template -->
    SEPARATOR "\n")=Products
|group by=Ingredients.Ingredient
}}

Finally, maybe we want to format the first column too. We can do that as well, but currently we know:

• The name of the ingredient
• The name of the product
• Extra information about the product (from the Items table)

We do NOT know:

• Extra information about the ingredient

So if we want to display something about the product’s element, we will need to add in an extra copy of Items, which we’ll join to Ingredients on Ingredients.Ingredient=Items2.Name (as opposed to using Ingredients.Product).

Then the info we’ll have is:

• The name of the ingredient (from Ingredients)
• The extra information about the ingredient, including the element (from Items2)
• The name of the products (from Ingredients)
• The extra information about the products, including the element (from Items)

Here’s the query:

{{#cargo_query:tables=Ingredients, Items, Items=Items2
|join on=Ingredients.Product=Items.Name, <!-- For the product element -->
         Ingredients.Ingredient=Items2.Name <!-- For the ingredient element -->
|fields=CONCAT(<!-- This on the "one" side of the "one-to-many", so CONCAT not GROUP_CONCAT -->
    "{{((}}ItemWithElement{{!}}Item=",
    Ingredients.Ingredient,
    "{{!}}Element=",
    Items2.Element, <!-- notice this is from Items2, not Items -->
    "{{))}}"
)=Ingredient,
    GROUP_CONCAT(
        "* {{((}}ItemWithElement{{!}}Item=", <!-- Using Template:(( and the magic word {{!}} for {{ and | -->
        Ingredients.Product,
        "{{!}}Element=", <!-- Using the magic word {{!}} for a literal | character -->
        Items.Element,
        "{{))}}" <!-- remember to close the template -->
    SEPARATOR "\n")=Products
|group by=Ingredients.Ingredient
}}

And the result:

CONCAT vs GROUP_CONCAT

In the last example, we saw that sometimes you can use CONCAT, and sometimes you need to use GROUP_CONCAT to get what you want. How to remember which is which?

• You don’t have to! You can use GROUP_CONCAT always, and “at worst” it’ll do the same thing CONCAT would do.

Further reading

I highly recommend the book Database Design for Mere Mortals. This is the book I first learned to design databases from.

Data and Reality is also a great text, it explains to you why all your assumptions are wrong and data is much more complicated than you think.