Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2015
    Posts
    1

    Question Unanswered: relation between items and tags

    hello

    I have the following MySql database structure:

    `items` table:
    id | itemid | title
    ---+---------------
    1 | 314 | Black hat
    2 | 587 | Golden Pencil
    `tags` table:
    id | tag
    ---+--------
    1 | clothes
    2 | pens
    3 | presents
    `items_tags` table:
    id | item | tag
    ---+------+----
    1 | 314 | 1
    1 | 314 | 3
    1 | 587 | 2
    1 | 587 | 3

    I'm trying to create an sql query to get all items including their tags in one query (I use it in the main for the main page).
    this is what I tried:

    SELECT i.* items, t.tag
    FROM items i
    JOIN items_tags it ON i.id = it.item
    JOIN tags t ON it.tag = t.id

    it gives a row of the item for every tag in database.
    for example if an item as 5 tags, it will give me 5 rows for that item.
    I need one row per item with tags as a string, or array or something.
    maybe an Inner join or a sub query.... I'm new to this but very willing to learn...

    can you help ?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •