I am using SQLite and have three tables (there is more data in these tables - abbreviated here):
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?
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 |