Mysql select based of results of another select -

i know can done scripting language, doing 1 sql call magnificent requirement. need select categories, , parent categories of product

say have these 2 tables.

table 1:categories || category || categoryid || =========================== || fightercraft      || 5      || spaceship    || 10    || ships   || 3    

and

table 2:catrelationships category || parentcategory =========================== 5  ||  10  10  || 3   3   ||  

my restriction i'm able pass 1 parameter query on first table categories. know have fightercraft category, need parent categories in single column.

select categories.category,catrelationships.category categories  join categories on catrelationships.category=categories.id categories.id=5 

need return somehow!

result || product     || category   || fightercraft || 5  || spaceship  || 10  || ships  || 3   

can steer me in right direction?

there may better way it, if you're willing assume there's limit on depth of nested categories, following should want:

select p.product      , c2.product category   products p   join (         select c1.category c1              , c1.parentcategory c2              , c2.parentcategory c3              , c3.parentcategory c4           categories c1      left join categories c2 on c1.parentcategory = c2.category      left join categories c3 on c2.parentcategory = c3.category         ) c on p.category = c.c1    join categories c2 on c2.category in (c.c1, c.c2, c.c3, c.c4)   order p.product asc, c2.category desc; 

the above query assumes maximum nested depth of 4 categories. can adjust depth necessary.

sql fiddle