Use SQLite SELECT to combine joined table column into one row for each category using a junction table

user_14904951 0
Viewed 200

I am using SQLite and have three tables (there is more data in these tables - abbreviated here):

  • Categories - Holds item categories (id, name, description)
  • Items - (id, name, status)
  • Reference (id, cat_id, item_id)

Categories can have many items associated with it or none. Items must have at least 1 category and may be in more than 1 category.

Example:

(Categories)
| id | name   | description                     |
|----|--------|---------------------------------|
| 1. | wet.   | something associated with water |
| 2. | dry.   | something else                  |
| 3. | metal. | steel, copper                   |

(Items)
| id. | name.   | status     |
|-----|---------|------------|
| 11. | river.  | north fork |
| 12. | lake.   | big        |
| 13. | river.  | south fork |
| 14. | desert. | mojave     |
| 15. | car.    | ford       |
| 16. | truck.  | chevy      |

(Reference)
| id | cat_id. | item_id |
|----|---------|---------|
| 21 | 1       | 11      |
| 22 | 1       | 12      |
| 23 | 2       | 14      |
| 24 | 3       | 15      |
| 25 | 3       | 16      |

Using the following:

SELECT c.name,(i.name || "-" || i.status) as Related from Items as i
join Categories c where c.id = cat.id

I get something that looks like this:

| c.name  | Related            |
|---------|--------------------|
| wet     | river - north fork |
| wet     | lake - big         |
| wet     | river - south fork |
| dry     | desert - mojave    |
| metal   | car - ford         |
| metal   | truck - chevy      |

What I need is

| c.name | Related                                            |
|--------|----------------------------------------------------|
| wet    | river - north fork, lake - big, river - south fork |
| dry    | desert - mojave                                    |
| metal  | car - ford, truck - chevy                          |

One category matched to the referenced items in the junction table and combined in the "Related" column (separated by comma in the example).

How do I get this result in SQLite?

junction-table group-concat select sqlite sql
4 Answers

You can use the group_concat as follows:

SELECT c.name, group_concat (i.name || "-" || i.status) as Related from Items as i
join Categories c on c.id = cat.id
group by c.name
user_115656290

You can write the query like this:

select c.name,
       group_concat(i.name || '-' || i.status, ', ') as Related
from Items i join
     Categories c 
     on c.id = i.cat_id
group by c.name;

Notes:

  • JOIN should always use ON for the JOIN conditions.
  • Use single quotes for strings in SQL.
  • The default separator is ','. You seem to want ', ', so you need the second argument.
  • You need GROUP BY.
user_11440350

You must join Categories to Reference first and then to Items, with LEFT joins, just in case a category does not have any items related and then aggregate with GROUP_CONCAT():

SELECT c.name,
       GROUP_CONCAT(i.name || ' - ' || i.status, ', ') Related
FROM Categories c
LEFT JOIN Reference r ON r.cat_id = c.id
LEFT JOIN Items i ON i.id = r.item_id
GROUP BY c.id, c.name

See the demo.
Results:

| name  | Related                        |
| ----- | ------------------------------ |
| wet   | river - north fork, lake - big |
| dry   | desert - mojave                |
| metal | car - ford, truck - chevy      |
user_104988280

The response from forpas was exactly what I needed. Thank you -

SELECT c.name,
  GROUP_CONCAT(i.name || ' - ' || i.status, ', ') Related
FROM Categories c
LEFT JOIN Reference r ON r.cat_id = c.id
LEFT JOIN Items i ON i.id = r.item_id
GROUP BY c.id, c.name
user_149049510
Related