CODEWITHSUNDEEP
mysql
CodeChap
CodeChap

Reputation: 4262

Merging MySQL row entries into a single row

I've got two tables, one for listings and another representing a list of tags for the listings table.

In the listings table the tag ids are stored in a field called tags as 1-2-3-. This has worked out very well for me (regular expressions and joins to separate and display the data), but I now need to pull the titles of those tags into a single row. See below.

listings table
id    tags
1     1-2-3-
2     4-5-6-


tags table
id    title
1     pig
2     dog
3     cat
4     mouse
5     elephant
6     duck

And what I need to produce out of the listings table is:

id     tags
2      mouse, elephant, duck

Upvotes: 1

Views: 143

Answers (3)

xycf7
xycf7

Reputation: 913

Here is a query that could help. But since it is doing some string operations, it may not be as good as a regular join:

select l.id, group_concat( t.title ) 
   from listings l, tags t 
   where concat( '-', l.tags )  like concat( '%-', t.id, '-%' ) group by l.id ;

Upvotes: 1

Rob Van Dam
Rob Van Dam

Reputation: 7960

Unfortunately, with your tags stored in this denormalized format, there's no easy way to go from 1-2-3 to the corresponding tags. In other words, there's no simple way to split out the ids, join to another table and then recombine. Your best option would be to create a listing_tag table with two columns

listing_id    tag_id
1             1
1             2
1             3
2             4
2             5
2             6

and then it's just a simple join:

SELECT listing_id, GROUP_CONCAT(title SEPARATOR ', ') FROM listing_tag JOIN tags ON tags.id = tag_id

Upvotes: 1

zerkms
zerkms

Reputation: 254926

GROUP_CONCAT() + INNER JOIN + GROUP BY

Upvotes: 0

Related Questions