Extract Data From Three MySQL Tables

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
BrahmanathaAsked:
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.

pcelbaCommented:
What you need is called Pivot table. The tutorial is e.g. here and here.

You may build the pivot query as a text string and then execute it by EXECUTE.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about simply using group_concat?
https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
select q.quote_id, q.quote_content
  , group_concat(c.Category_name) categories
from quote q
left join category_quote cq
  on cq.quote_id = q.quote_id
left join category c
   on c.category_id = cq.category_id

Open in new window

1
BrahmanathaAuthor Commented:
@ Guy

group_concat looks awesome

only thing is: this doesn't work (adjusted to actual column names)

select q.quote_id, q.content
  , group_concat(c.name) categories
from quote q
left join category_quote cq
  on category_quote.quote_id = quote.quote_id
left join category c
   on c.category_id = cq.category_id

Open in new window

 
  result:  #1054 - Unknown column 'category_quote.quote_id' in 'on clause'

being a total newbie to this level of complex join query... and never having seen this notation where a table name is passed to a "short cypher"  (category c.... where can I read about that?) -- on a hunch I just changed category_quote to cq and "quote" to "q  in that first join
SELECT q.quote_id, q.content, group_concat( c.name ) categories
FROM quote q
LEFT JOIN category_quote cq 
ON cq.quote_id = q.quote_id  # here  we switch to "short table labels" 
LEFT JOIN category c ON c.category_id = cq.category_id 

Open in new window


OK, so now the query compiles, runs but we get a single quote #1 with *all* the categories (150+) which have been truncated for some reason and displayed with ellipses... which is strange.. because why would MySQL not return the full data set (all categories) . it's not a lot of text....

This was still encouraging... because it appears we are close... but "no banana for us" yet

quote_id 	content 	categories 	
1 	The thirty-six elements dance. Sadāśiva dances. Co...	Realm of God,Siva,Metaphysics,Testimonial,Sadhana,...

Open in new window


Here's my *real
* schema.. again, in case it helps.

TABLE category (
  category_id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  PRIMARY KEY (category_id)


TABLE category_quote (
  category_quote_id int(11) NOT NULL AUTO_INCREMENT,
  category_id int(11) NOT NULL,
  quote_id int(11) NOT NULL,
  PRIMARY KEY (category_quote_id)

CREATE TABLE quote (
  quote_id int(11) NOT NULL AUTO_INCREMENT,
  content text NOT NULL,
  PRIMARY KEY (quote_id) have more columns, but I don't show those, only our targets.

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.

BrahmanathaAuthor Commented:
To state the obvious: for now we want a list of *all* quotes with their categories... but we are only getting the first record of the table and then the query stops.

We can look at incorporating "WHERE"  clauses later, to refine searchs, later after this gets going. (I will start a separate question then....)
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, I forgot the "obvious" part: the "GROUP BY"
SELECT q.quote_id, q.content, group_concat( c.name ) categories
FROM quote q
LEFT JOIN category_quote cq 
ON cq.quote_id = q.quote_id   
LEFT JOIN category c ON c.category_id = cq.category_id 
GROUP BY q.quote_id, q.content
                                          

Open in new window


to read about the "short stuff": read my article here about "aliases"
http://www.experts-exchange.com/articles/11135/Why-should-I-use-aliases-in-my-queries.html
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
BrahmanathaAuthor Commented:
Fantastic... it works... this was a blocker for me and in less than 15 hours I'm back on track with my app thanks to Guy... If i could sent a case of mangoes over to him and the EE team from Kauai I would!  Thanks Guy!
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.