dynamically create row MySql
I've been working with joomla 1.6, and i need to use the same column of a table twice:
there are categories, and each category can have subcategories.
in another table i have an id pointing to the category, so then i create a JOIN to replace the id with the 'title' (the description of the category)
let's say i have this categories:
- PARENT1
* SUB1
* SUB2
- PARENT2
* SUB1
* SUB2
TABLE CATEGORIES:
id | title | parent_id
--------------------------------------------
0 | ROOT | NULL
1 | PARENT1 | 0开发者_开发百科
2 | SUB1 | 1
3 | SUB2 | 1
4 | PARENT2 | 0
5 | SUB1 | 4
6 | SUB2 | 4
--------------------------------------------
TABLE DATA:
id | name | cat_id
--------------------------------------------
0 | p1_sub1 | 2
1 | p1_sub1 | 2
2 | p1_sub2 | 3
3 | p2_sub1 | 5
4 | p2_sub2 | 6
5 | p1_sub2 | 3
6 | p2_sub2 | 6
--------------------------------------------
so i use this query to get the corresponding title for the cat_id:
SELECT data.name, cat.title FROM table_data AS data
LEFT JOIN table_categories AS cat ON data.cat_id = cat.id
this works fine, but now i also want to get the title
from the parent category
:
TABLE RESULT:
name | title | parent_title
--------------------------------------------
p1_sub1 | SUB1 | :S
--------------------------------------------
i mean, to do this i should make another join cat.parent_id = cat.id
, but i need to add another title
column to the query so...
any ideas????
THNX!!!
This should work:
SELECT data.name AS name, cat.title AS title, cat2.title AS parent_title
FROM table_data AS data
LEFT JOIN table_categories AS cat ON data.cat_id = cat.id
LEFT JOIN table_categories AS cat2 ON cat.parent_id = cat2.id
精彩评论