MySQL: Selecting an entry's parent and grandparent names -

on site, have several articles, , each article assigned location category. how has been set quite time, can't change how works.

the relationship between these set in 2 tables, categories , category_posts:

categories  +--------+------------------+------------------+-----------+ | cat_id | cat_name         | cat_url_title    | parent_id | +--------+------------------+------------------+-----------+ |  1     | toronto          | toronto          | 2         | |  2     | ontario          | ontario          | 3         | |  3     | canada           | canada           | 0         | |  4     | vancouver        | vancouver        | 5         | |  5     | british columbia | british_columbia | 3         | |  6     | montreal         | montreal         | 7         | |  7     | quebec           | quebec           | 3         | |  8     | san francisco    | san_francisco    | 9         | |  9     | california       | california       | 10        | |  10    | united states    | united_states    | 0         | +--------+------------------+------------------+-----------+  category_posts  +----------+--------+ | entry_id | cat_id | +----------+--------+ | 990      | 1      | | 991      | 4      | | 992      | 8      | | 993      | 6      | | 994      | 4      | | 995      | 4      | | 996      | 1      | | 997      | 6      | | 998      | 8      | | 999      | 4      | | 1000     | 3      | | 10001    | 10     | +----------+--------+ 

so category posts contains list of articles' entry id's, , category have been assigned. categories table lists categories, including parents , url titles.

what i'm trying write query article entry id, returns category name , url title, of of parents , grandparents.

so example, if used 999 entry id, result want:

+-----------+---------------+------------------+------------------+------------------+-----------------------+ | cat_name  | cat_url_title | parent_name      | parent_url_title | grandparent_name | grandparent_url_title | +-----------+---------------+------------------+------------------+------------------+-----------------------+ | vancouver | vancouver     | british columbia | british_columbia | canada           | canada                | +-----------+---------------+------------------+------------------+------------------+-----------------------+ 

now, i'm kind of stuck writing query that. know how entry's own category name , url title using query:

select cat_name, cat_url_title categories join category_posts on categories.cat_id=category_posts.cat_id category_posts.entry_id = 999; 

but how include parent , grandparent categories? makes sense.

edit: realized modification necessary. can't child categories selected category every article. parent , grandparent categories selected. when put such article's entry id query, returns no results. updated category_posts table show this. know how can this?

you can join 3 instances of categories table that:

select  child.cat_name child_cat_name, child.cat_url_title child_cat_url_title,  parent.cat_name parent_cat_name, parent.cat_url_title parent_cat_url_title,  grandparent.cat_name grandparent_cat_name, grandparent.cat_url_title grandparent_cat_url_title  categories child  join category_posts on child.cat_id=category_posts.cat_id join categories parent on child.parent_id = parent.cat_id join categories grandparent on parent.parent_id = grandparent.cat_id category_posts.entry_id = 999;