Sqlite select from multiple tables-CodePudding

I have five tables such as

Base Table

| group_id | group_name  |
|----------|-------------|
| 1        | gn1         |
| 2        | gn2         |
| 3        | gn3         |

"Tags" Table

| tag_id | tag_name |
|--------|----------|
| 1      | tgn1     |
| 2      | tgn2     |
| 3      | tgn3     |

"Theme" Table

| theme_id | theme_name |
|----------|------------|
| 1        | thn1       |
| 2        | thn2       |
| 3        | thn3       |

"Tags" Mapping Table

| rec_id | group_id | tag_id |
|--------|----------|--------|
| 1      | 1        | 2      |
| 2      | 1        | 3      |
| 3      | 2        | 1      |

"Theme" Mapping Table

| rec_id | group_id | theme_id |
|--------|----------|----------|
| 1      | 1        | 2        |
| 2      | 2        | 3        |
| 3      | 2        | 1        |

I am having some trouble creating a sqlite query to get table like this:

| group_id | group_name | tags       | themes     |
|----------|------------|------------|------------|
| 1        | gn1        | tgn2, tgn3 | thn2       |
| 2        | gn2        | tgn1       | thn3, thn1 |
| 3        | gn3        |            |            |

CodePudding user response：

A pretty simple method uses correlated subqueries:

select b.*,
       (select group_concat(t.tag_name)
        from tag_mapping tm join
             tags t
             on tm.tag_id = t.tag_id
        where tm.group_id = b.group_id
       ) as tags,
       (select group_concat(t.theme_name)
        from theme_mapping tm join
             themes t
             on tm.theme_id = t.theme_id
        where tm.group_id = b.group_id
       ) as themes
from base b;

CodePudding user response：

The group_concat function will do the trick - join all the tables, group by the group id and name, and group_concat the other details:

SELECT   g.group_id, 
         g.group_name,
         GROUP_CONCAT(t.tag_name, ', ') AS tags
         GROUP_CONCAT(th.theme_name, ', ') AS theme
FROM     groups g
JOIN     tags_map tg ON g.group_id = tm.group_id
JOIN     tags t ON t.tag_id = tm.tag_id
JOIN     themes_map thm ON g.group_id = thm.group_id
JOIN     themes the ON th.theme_id = thm.theme_id
GROUP BY g.group_id, g.group_name

CodePudding user response：

Join the tables, group by each row of the Base table and use GROUP_CONCAT() with the DISTINCT keyword to remove duplicates returned from the multiple joins:

SELECT b.group_id, b.group_name,
       GROUP_CONCAT(DISTINCT tg.tag_name) tags,
       GROUP_CONCAT(DISTINCT th.theme_name) themes
FROM Base b
LEFT JOIN TagsMap tgm ON tgm.group_id = b.group_id
LEFT JOIN Tags tg ON tg.tag_id = tgm.tag_id
LEFT JOIN ThemeMap thm ON thm.group_id = b.group_id
LEFT JOIN Theme th ON th.theme_id = thm.theme_id
GROUP BY b.group_id, b.group_name;

See the demo.