asked on
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
MySQL ServerDatabases
Last Comment
Brahmanatha
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
ASKER
@ 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.
ASKER
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....)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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!
You may build the pivot query as a text string and then execute it by EXECUTE.