There is a site with texts where each text falls into 2-3 different categories. (Thanks to the PHPBuilder community, I was able to implement that. 🙂) The tables are as follows:
TEXTS
text_id----text_title----text_content
1 --------- Cat---------asdfasdfasdf
2 --------- Crocodile----asdfasdfasdf
TEXT_CAT
cat_id---- cat_name
1- -------- pet
2 --------- small
3 --------- green
4 --------- red
5 --------- big
TEXT_MAP
map_id-- text_id----cat_id
1 --------- 1 ---------- 1
2 --------- 1 ---------- 2
3 --------- 1 ---------- 4
4 ---------- 2---------- 3
5 ---------- 2 ---------- 5
First I run the main query to display the list of texts in a certain category:
$main_query = mysql_query("SELECT texts.text_id, texts.text_title, texts.text_content, text_cat.cat_name
FROM texts, texts_cat, text_map
WHERE text_cat.cat_id = '$cat_id'
AND text_map.cat_id = texts_cat.cat_id
AND text_items.text_id = text_map.text_id");
Then - for every list item - the auxiliary query is executed to select all the categories the item belongs to:
$auxiliary_query = mysql_query("SELECT DISTINCT text_map.cat_id, text_cat.cat_name
FROM text_cat, text_map
WHERE text_map.text_id = '$text_id'
AND text_cat.cat_id = text_map.cat_id");
Everything works like a charm and I get the desired output:
Cat
Categories: small, red, pet
Crocodile
Categories: green, big
Here comes the problem: I want to get rid of the auxiliary query. Is there a way to combine the two queries in one? In other words, is it possible to select a row like that with ONE query?
text_id---text_title---cat_name------------cat_id
1----------Cat --------pet, small, red----1, 2, 4
Since I have MySQL 3.23, subqueries are not applicable... Any help would be highly appreciated.
