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
You may build the pivot query as a text string and then execute it by EXECUTE.