[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL Joins

Posted on 2011-10-15
6
Medium Priority
?
479 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:Julian Matz
  • 3
  • 2
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 36972847
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
 
LVL 15

Assisted Solution

by:Insoftservice
Insoftservice earned 400 total points
ID: 36973372
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
 
LVL 21

Author Comment

by:Julian Matz
ID: 36974120
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 36974215
that would be a "bad" design ...
0
 
LVL 21

Author Comment

by:Julian Matz
ID: 36974312
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
 
LVL 21

Author Comment

by:Julian Matz
ID: 36974417
Got it working now. Thanks for your help!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 10 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question