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

Question

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?

Popeye · Answer 1 · 2020-12-29T15:00:36.863

1

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

edited Dec 29 '20 at 15:00

answered Dec 29 '20 at 05:24

Popeye

35,427
4
10
31

Thank you for your response. The problem I'm having is that the Categories and Items tables only have the Reference table to link them (many to many). I need something like "Foreach Category => lookup all Items in the Reference table and return all Items in one column". Each Category on a separate row showing all Items associated based on the Reference table. – Vento Dec 29 '20 at 14:54
Yes, the updated answer will fetch the desired result. – Popeye Dec 29 '20 at 15:00
Thanks Popeye. This does help, but I needed the reference from the junction table to pull them all in under one row (see the response from Gordon). I hope I'm not still missing your point because I really appreciate the response and don't want to discount the suggestion if I am still not seeing it. Glad to know there are so many good people out there willing to help out. – Vento Dec 29 '20 at 21:13

Gordon Linoff · Answer 2 · 2020-12-29T14:59:06.570

1

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.

edited Dec 29 '20 at 14:59

answered Dec 29 '20 at 13:49

Gordon Linoff

1,242,037
58
646
786

Thanks for your post and quick response. I think there is still a missing part though - "from Items i join Categories c on c.id = cat.id" - cat.id is not defined in the response and should come from the Reference table. In other words, I need the association with the reference table to find all of the items associated with the category. – Vento Dec 29 '20 at 14:47
@Vento . . . That was your original condition. It shouldn't work in your query either. I fixed the answer. – Gordon Linoff Dec 29 '20 at 14:59
Yes, you're right Gordon, that was my bad for sure. I see your response and really appreciate the help and feedback. – Vento Jan 04 '21 at 15:16

forpas · Accepted Answer · 2021-01-04T15:37:46.443

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      |

score 0 · Answer 4 · answered Jan 04 '21 at 15:25

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

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

4 Answers4