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.
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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
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.
what about simply using group_concat?
https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_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
@ Guy
group_concat looks awesome
only thing is: this doesn't work (adjusted to actual column names)
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
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
Here's my *real
* schema.. again, in case it helps.
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
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
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,...
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.
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....)
We can look at incorporating "WHERE" clauses later, to refine searchs, later after this gets going. (I will start a separate question then....)
ok, I forgot the "obvious" part: the "GROUP BY"
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
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
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
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
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!
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
You may build the pivot query as a text string and then execute it by EXECUTE.