How to build mysql query for this?

nsatheesh87 · #1 (**permalink**) 03-26-10, 06:32

table1:dynamic_info
catgory_id page_id title
6 1 test
table2:category
category_id parent_id category_name
1 0 travel
6 1 car

This is my table structure.

Here What i need is.

output:TRavel car test

In table 2
if parent id is 0 means it is category'
if parentid is 1 means car is the subcategory of catgory travel..
I cant build this one.. please help me

r937 · #2 (**permalink**) 03-26-10, 06:53

Code:

SELECT parent.category_name
     , child.category_name AS subcategory_name
     , dynamic_info.title
  FROM category AS parent
INNER
  JOIN category AS child
    ON child.parent_id = parent.category_id
INNER
  JOIN dynamic_info
    ON dynamic_info.catgory_id = child.category_id