Inner joining to get all tags for an item

Databases
1

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

look up the GROUP_CONCAT function

also, your query as posted is invalid, you cannot assign the alias “items” to i.*

you should also consider my suggestion that i gave on, ahem, another forum, to stop using an auto_increment id for the tags

3

I need a sql query for tables I provided.
don’t take this the the wrong way but if you don’t want to give a sql query I need, please let someone else reply my threads and stop following me all over the internet.
I am posting at different forums in order to get away from you so I can get another opinion, unless you own all these forums - please understand that I am entitled of a reply from someone else.

thank you for understanding.

4

Easy there davidi if you please, r937 has been a member here for an extremely long time, years if not a decade or longer.

What you will find here is that most members are more than willing to help you learn. That is less likely to happen if you are simply handed the solution without putting some effort into it.

Have you

5

hi, Mittineague nice to see a different face other than r937.
don’t get me wrong , I am sure he is great and he seems knowledgeable but I can’t get away from him… he is chasing me on every forum I post and it is really getting creepy and scary for me.
I needed a another opinion and not sure about using GROUP_CONCAT because it seems like what I’m trying to do might be possible with inner joins or sub queries.
is GROUP_CONCAT really the conventional way to do this ?

6

If he knew of your recent arrival (BTW, welcome to the forum) ~12 or so years ago when he joined he’s more knowledgeable than most Not exactly what I would call chasing, but more an extremely long ambush perhaps. :wink:

It very well may be possible.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

To eliminate duplicate values, use the DISTINCT clause

mysql> SELECT student_name,
    ->     GROUP_CONCAT(DISTINCT test_score
    ->               ORDER BY test_score DESC SEPARATOR ' ')
    ->     FROM student
    ->     GROUP BY student_name;
7

[quote=“Mittineague, post:6, topic:108941”]To eliminate duplicate values, use the DISTINCT clause[/quote]yeah, but why would a single item have more than one instance of the same tag?

as for “chasing me on every forum I post and it is really getting creepy and scary for me” – that’s a bit overblown… it is exactly ~one~ other forum besides this one, devshed.com, where i have been a member for about 12 years and for most of them a moderator of the database forums

anyhow, GROUP_CONCAT is what he’s looking for, and that should be completely sufficient in the way of advice

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.