2

I would like to do some new stuff (for me it's new, bc. I'm just a MySQL-beginner) and I did not find a solution for this.

I got these entries in my database:

mytable_items
id | title | catids
1 | test | 32,14

mytable_categories
id | title
32 | Test-Category
14 | Another-Category

Now I would like to join this stuff: Show all data from mytable_items - also show the assigned categories (their titles)

The result should be:

1 | test | Test-Category, Another-Category

How can I solve this?

Thanks a lot in advance :-)

MyFault
  • 427
  • 1
  • 6
  • 21
  • 1
    Start from [**Database Normalization**](http://en.wikipedia.org/wiki/Database_normalization) – M Khalid Junaid May 05 '15 at 12:38
  • recommend splitting `catids` into a link table, if they are in one field, you will have to recursively substring and link.. [ not easy or recommended ] – amdixon May 05 '15 at 12:39

3 Answers3

1

Try this:

SELECT  m.id,group_concat(mc.title)
FROM    mytable_items m
JOIN    mytable_categories mc
ON      FIND_IN_SET(mc.id,m.catids)
group by
    m.id

SQL FIDDLE DEMO

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • I'm not sure that SO should be encouraging this kind of solution - especially not for a beginner! – Strawberry May 05 '15 at 13:06
  • @MyFault:- You are welcome. Also to inform you that it is not recommended to store the values as comma seperated specially ids in your table. It will create trouble for you in future. Try to learn Normalisation as suggested – Rahul Tripathi May 05 '15 at 13:10
-1

You should use different entities for each CatID. Then you can join both tables and use Group Concat.

Community
  • 1
  • 1
Tobias Baumeister
  • 2,107
  • 3
  • 21
  • 36
-1

Try this:

Select * 
from mytable_items a
join mytable_categories b on a.id = b.id

This will join the data and show it correctly.

kamal
  • 195
  • 2
  • 9