I have these three tables:
id | owner_id | value | ----------------------- 1 | 1 | 1337 | 2 | 2 | 1337 | 3 | 2 | 1337 | 4 | 1 | 1337 |
id | owner_id | text | --------------------------- 1 | 1 | 'Tag 01' | 2 | 1 | 'Tag 02' | 3 | 1 | 'Tag 03' | 4 | 2 | 'Tag 04' |
id | owner_id | tag_id | value_id | ----------------------------------- 1 | 1 | 1 | 1 | 2 | 1 | 2 | 1 | 3 | 1 | 3 | 1 |
So basically, users can submit values and enter any number of freetext tags to attach to that value. I need to store the tags as belonging to a specific user, and I need to be able to count how many times they've used each tag.
What I want to accomplish is a query that gets rows from user_submitted_value with the tags appended onto them. For example:
Query value with id 1: id | owner_id | value | tags | ------------------------------------------------------ 1 | 1 | 1337 | "'Tag 01','Tag 02','Tag 03'" | Query all values belonging to user with id 1: id | owner_id | value | tags | ------------------------------------------------------ 1 | 1 | 1337 | "'Tag 01','Tag 02','Tag 03'" | 4 | 1 | 1337 | "" |
I know I need to JOIN one or more times, somehow, but I am not comfortable enough with SQL to figure out exactly how.
This seems like a rather arcane data format -- particularly because owner_id is repeated in all the tables.
In any case, I think the basic query that you want to get the values and tags for a given user looks like this:
select usv.owner_id,
group_concat(distinct usvt.value_id) as values,
group_concat(distinct t.text) as tags
from user_submitted_value usv join
user_submitted_value_tag usvt
on usv.value_id = usvt.value_id and usv.owner_id = usvt.owner_id join
tags t
on usvt.tag_id = t.id and usvt.owner_id = t.owner_id
group by usv_owner_id;