INNER JOIN ON SELF (or same effect)

[knowj]

I have a categories table with the following structure:
category_id
category_name
category_url
category_parent //contains the category_id of parent or 0
live

How would I go about joining the subcategories onto the parent within a single query to basically convert the category_parent into the parent category url

row 1: 1, parent cat 1, parent-cat-1, 0, 1
row 2: 2, sub cat 1, sub-cat-1, 1, 1
row 3: 3, sub cat 2, sub-cat-1, 1, 1

My aim is to be able to run a query along the following lines:

SELECT * FROM category WHERE category_url='sub-category-1' AND category_parent='parent-category-1'

thanks in advance for any input

[Weedpacket]

You need to distinguish between the two uses of the category table.
"....from category cat, category parent where ...."
and prefix the columns you use with "cat." or "parent." as appropriate. Like, if you use the category_id from the parent table, it would be "parent.category_id".