MySQL Joins

Hi! Please consider the following MySQL tables/columns:

table listings
__________________________________
id (int) | title (varchar255) |
_________|____________________|___
    1    |      title1        |
    2    |      title2        |
    3    |      title3        |
_________|____________________|___


table categories
__________________________________
id (int) | title (varchar255) |
_________|____________________|___
    1    |      categoryA     |
    2    |      categoryB     |
    3    |      categoryC     |
_________|____________________|___


table relationships
____________________________________________
id (int) |   listing   |   category     |
_________|_____________|________________|___
    1    |       1     |       1        |
    2    |       2     |       1        |
    3    |       2     |       3        |
    3    |       3     |       2        |
_________|_____________|________________|___

Open in new window


Is there a way to join these tables so that each row from the table "listings" will be listed with the associated categories from the table "categories" according to the table "relationships" or would you need to do a separate query?

For example:

Listing          Categories
---------------------------------
title1            categoryA
title2            categoryA, CategoryC
title3            categoryB

Or is there a better way to do what I'm trying to achieve? I'm using InnoDB tables.

Thanks.
LVL 21
Julian M.Web DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should
select l.title, group_concat(c.title) categories
from listings l
left join relationships r
  on r.listing = l.id
left join categories c
  on c.id = r.category
group by l.id, l.title

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
InsoftserviceCommented:
i agree with angelll


select l.title, group_concat(c.title) as categories
from listings l
left join relationships r
  on (r.listing = l.id)
left join categories c
  on (c.id = r.category)
group by l.id, l.title

It will be fine, but i just want to know is it necessary to use inodb . If i am not wrong you might not be requiring roll back facility or nor be using some sensitive data .
Its recommended to use myisam .
0
Julian M.Web DeveloperAuthor Commented:
Can't get this working. It seems to return only one record from the "listings" table. How would it work if I had only the two tables, listings and categories, and if the listings table had a column called "categories" with a comma-separated string containing the category IDs? e.g.:

______________________________________________
id (int) | title (varchar255) |  categories  |
_________|____________________|______________|
    1    |      title1        |     1,10     |
    2    |      title2        |    2,5,10    |
    3    |      title3        |       4      |
_________|____________________|______________|

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
that would be a "bad" design ...
0
Julian M.Web DeveloperAuthor Commented:
OK then, back to the first idea...

When I used the following in my statement, categories was empty:
GROUP BY l.id, l.title

Open in new window


It worked when I changed it to just:
GROUP BY l.id

Open in new window


But only one result from the table "listings" is returned.
0
Julian M.Web DeveloperAuthor Commented:
Got it working now. Thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.