We help IT Professionals succeed at work.
Get Started

Extract Data From Three  MySQL Tables

Brahmanatha
Brahmanatha asked
on
106 Views
Last Modified: 2015-08-11
We have this new scenario on our web server... getting away from using enumerated lists for data... seems sweet in the beginning but later has serious limitations... switching to more complex relational tables... robust flexibility, comes with cost of query complexity.

Table:  Category...

category_id  | Category_name

1       God
2       Soul
3       World
4       Mind
5       Life

etc.

Table: quote

quote_id        quote_content

1                    Meditation is good for you
2                    Don't eat too much
3                    Exercise daily
4                    Get away from the screen at least 48 hours each week!

to apply categories to quotes (one quote can have multiple categories)


e.g.  quote:  "Don't eat too much."   categories:  World, Life

we have set up another relational table like this

category_quote

category_quote_id  | quote_id   | category_id

1       2          3
1       2          5

i.e. there are two records showing two categories attached to quote_id  "2"

so now... I want to query the data base for all quotes and the categories assigned to them

so the output looks like

"Don't eat too much"   World, Life

I use LiveCode and I could do some horrible hack and get all the quote records on a loop, then on each iteration do a query of the category_quote table for a match, and then another query on the category table to extract the name of the category... but this will be very, very process intensive... I'm hoping there is a query for this.

I have looked at inner and outer, right and left joins, but they all seem to only do

table   cars
table colors

and you get
VW Brown
Mercedes  Silver

but I don't see query example examples across three tables that will give me

VW  Brown, Silver
Comment
Watch Question
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE