Join a table to itself

[Anon]

I have a table (called categories) with the following fields:
cat_id, parent_id, cat_name
In this table all the main categories have as parent_id the value "0" and as cat_id another value.
The sub categories have as their parent_id the cat_id of the main category they belong to.

How can i produce a result that contains the following fields :
parent_id, cat_id, cat_name, parent_name

In the parent_name i want to show for each subcategory the name of the main category it belongs to and i want to get all the categories (main and subs).

I tried it with joining the table to itself with no success.
Some code would be helpful.

Thanks

John

[Anon]

Make two tables. Your life will be easier that way. Unless you absolutely must set your tables up like that, then I strongly recommend the two tables. It'll not only make this easier now, but it'll make a lot more things easier in the future.

I know, I know, you wanted a straight answer. Oh well.

:-)

Aaron Zoller

[Anon]

SELECT cat_top.cat_id AS parent_id,
cat.cat_id as subcat_id,
cat.cat_name as subcat_name,
cat_top.cat_name AS parent_name
FROM categories cat_top
LEFT OUTER JOIN categories cat ON (cat_top.cat_id = cat.parent_id);

You will get a return on each row per parent_id as well as a one row for a top level category without subcategories.