Combining duplicate SQLite columns after joining parent with multiple children

Question

I've got a SQLite database set up which looks like this:

I'm currently looking to join all of the child tables into the parent table. As you can see, all of the children are designed in the same way, so I was hoping that they would merge nicely together.
Unfortunately, joining the tables with a left join results in this:

With SQLite, is it possible to merge the columns so that the null values are ignored, and the remaining starting_item_ids and item_ids all display in the same column?

Any help would be greatly appreciated.

Edit: Here's a minimal reproducible example

CREATE TABLE `starting_item` (
  `starting_item_id` integer PRIMARY KEY AUTOINCREMENT,
  `quantity` integer
);

CREATE TABLE `starting_weapon` (
  `starting_item_id` integer,
  `item_id` integer,
  FOREIGN KEY (`starting_item_id`) REFERENCES `starting_item` (`starting_item_id`)
);

CREATE TABLE `starting_armor` (
  `starting_item_id` integer,
  `item_id` integer,
  FOREIGN KEY (`starting_item_id`) REFERENCES `starting_item` (`starting_item_id`)
);

CREATE TABLE `starting_gear` (
  `starting_item_id` integer,
  `item_id` integer,
  FOREIGN KEY (`starting_item_id`) REFERENCES `starting_item` (`starting_item_id`)
);

CREATE TABLE `starting_tool` (
  `starting_item_id` integer,
  `item_id` integer,
  FOREIGN KEY (`starting_item_id`) REFERENCES `starting_item` (`starting_item_id`)
);


INSERT INTO starting_item (quantity) VALUES (1);
INSERT INTO starting_item (quantity) VALUES (1);
INSERT INTO starting_item (quantity) VALUES (1);
INSERT INTO starting_item (quantity) VALUES (1);


INSERT INTO starting_weapon (starting_item_id, item_id) VALUES (1, 4);
INSERT INTO starting_armor (starting_item_id, item_id) VALUES (2, 7);
INSERT INTO starting_gear (starting_item_id, item_id) VALUES (3, 30);
INSERT INTO starting_tool (starting_item_id, item_id) VALUES (4, 20);


select * from starting_item 
    left join starting_weapon on starting_weapon.starting_item_id = starting_item.starting_item_id
    left join starting_armor on starting_armor.starting_item_id = starting_item.starting_item_id
    left join starting_gear on starting_gear.starting_item_id = starting_item.starting_item_id
    left join starting_tool on starting_tool.starting_item_id = starting_item.starting_item_id

Current result:

+------------------+----------+------------------+---------+------------------+---------+------------------+---------+------------------+---------+
| starting_item_id | quantity | starting_item_id | item_id | starting_item_id | item_id | starting_item_id | item_id | starting_item_id | item_id |
+------------------+----------+------------------+---------+------------------+---------+------------------+---------+------------------+---------+
|                1 |        1 |                1 |       4 |                  |         |                  |         |                  |         |
+------------------+----------+------------------+---------+------------------+---------+------------------+---------+------------------+---------+
|                2 |        1 |                  |         | 2                | 7       |                  |         |                  |         |
+------------------+----------+------------------+---------+------------------+---------+------------------+---------+------------------+---------+
|                3 |        1 |                  |         |                  |         | 3                | 30      |                  |         |
+------------------+----------+------------------+---------+------------------+---------+------------------+---------+------------------+---------+
|                4 |        1 |                  |         |                  |         |                  |         | 4                | 20      |
+------------------+----------+------------------+---------+------------------+---------+------------------+---------+------------------+---------+

Intended Result:

+------------------+----------+------------------+---------+
| starting_item_id | quantity | starting_item_id | item_id |
+------------------+----------+------------------+---------+
|                1 |        1 |                1 |       4 |
+------------------+----------+------------------+---------+
|                2 |        1 |                2 |       7 |
+------------------+----------+------------------+---------+
|                3 |        1 |                3 |      30 |
+------------------+----------+------------------+---------+
|                4 |        1 |                4 |      20 |
+------------------+----------+------------------+---------+

user_7733418 · Answer

You can have the stated desired output (minus frames, but that is just a config thing), with this query:

select * from starting_item a
join (      select * from starting_weapon
      union select * from starting_armor
      union select * from starting_gear
      union select * from starting_tool) b
on a.starting_item_id=b.starting_item_id;

It uses a multi "union" of the identically structured subtables and joins it with the starting item table, using aliases "a" and "b".

The result is quite exactly the stated desired output:

starting_item_id  quantity    starting_item_id  item_id
----------------  ----------  ----------------  ----------
1                 1           1                 4
2                 1           2                 7
3                 1           3                 30
4                 1           4                 20

However, in my opinion avoiding the redundant column (twice the ID) is desireable.
And I also feel somehow that a little trick to add an indicator (what kind of item we are seeing) is helpful. So I offer this for prettiness:

select * from starting_item
join (      select *, "weapon" kind from starting_weapon
      union select *, "armor"  kind from starting_armor
      union select *, "gear"   kind from starting_gear
      union select *, "tool"   kind from starting_tool)
using(starting_item_id);

It gets you, in case you like it:

starting_item_id  quantity    item_id     kind
----------------  ----------  ----------  ----------
1                 1           4           weapon
2                 1           7           armor
3                 1           30          gear
4                 1           20          tool

The first change is to join with using() instead of on ..., which (in spite of using * for convenience) gets you only one ID column.
The second change is to add a column named "kind" with explicitly given content in each "unioned" part. This "unions" and "joins" up nicely and gets you a named indicator column. The advantage is that you can e.g. order by that indicator column for some tidyness.

Finally, please consider whether the unioned table used here is not actually something which you can use in your database design. I.e. use one table for all items, with an additional "kind" column.

Combining duplicate SQLite columns after joining parent with multiple children

1 Answers