开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜