Avatar of Brahmanatha
 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


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_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

Avatar of undefined
Last Comment

8/22/2022 - Mon
Pavel Celba

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.
Guy Hengel [angelIII / a3]

what about simply using 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


@ 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)

  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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

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....)
Guy Hengel [angelIII / a3]

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.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

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!