CODEWITHSUNDEEP
mysqljoindatabase-normalization
korona
korona

Reputation: 2230

Joining three tables to get a list of tags

I have these three tables:

user_submitted_value

  id | owner_id | value |
  -----------------------
   1 | 1        |  1337 |
   2 | 2        |  1337 |
   3 | 2        |  1337 |
   4 | 1        |  1337 |

tag

  id | owner_id |      text |
  ---------------------------
   1 | 1        |  'Tag 01' |
   2 | 1        |  'Tag 02' |
   3 | 1        |  'Tag 03' |
   4 | 2        |  'Tag 04' |

user_submitted_value_tag

  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.

Upvotes: 0

Views: 40

Answers (2)

korona
korona

Reputation: 2230

Here's the final solution in my case. Heavily based on the answer submitted by Gordon Linoff.

      SELECT
        user_submitted_value.id,
        user_submitted_value.creator_id,
        user_submitted_value.value,
        group_concat(tag.text) AS tags
      FROM user_submitted_value
      LEFT JOIN user_submitted_value_tag
        ON user_submitted_value.id = user_submitted_value_tag.value_id
        AND user_submitted_value.creator_id = user_submitted_value_tag.creator_id
      LEFT JOIN tag
        ON user_submitted_valuetag.tag_id = tag.id
        AND user_submitted_value_tag.creator_id = tag.creator_id
        WHERE user_submitted_value.id = ?
      GROUP BY user_submitted_value.id

The WHERE clause on the second JOIN can be modified to get all values for a given user.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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;

Upvotes: 2

Related Questions