Fetching rows comparing values in same table

Hi.
I had one table organized like

entry_id (int)
parent (int)

so i need to create menu from database.
Every entry that parent = 0 is topmost level of menu. Every entry that
has no child is article.
I write recursive function that fetch rows but I need to test every row
in db, so if any of you know how to fetch only that rows that has
children (other row parent is row id).

Also, it would be great if is MySQL 4 (but any solution is great)
compatible.

Thanx.

Re: Fetching rows comparing values in same table

FatCoin wrote:
> Hi.
> I had one table organized like
>
> entry_id (int)
> parent (int)
>
> so i need to create menu from database.
> Every entry that parent = 0 is topmost level of menu. Every entry that
> has no child is article.
> I write recursive function that fetch rows but I need to test every row
> in db, so if any of you know how to fetch only that rows that has
> children (other row parent is row id).

SELECT DISTINCT parent FROM table WHERE parent>0;

This will give you all parents and each parent should be in the list just once
too.


//Aho

Re: Fetching rows comparing values in same table

J.O. Aho wrote:
> FatCoin wrote:
>
>>Hi.
>>I had one table organized like
>>
>>entry_id (int)
>>parent (int)
>>
>>so i need to create menu from database.
>>Every entry that parent = 0 is topmost level of menu. Every entry that
>>has no child is article.
>>I write recursive function that fetch rows but I need to test every row
>>in db, so if any of you know how to fetch only that rows that has
>>children (other row parent is row id).
>
>
> SELECT DISTINCT parent FROM table WHERE parent>0;
>
> This will give you all parents and each parent should be in the list just once
> too.
>
>
> //Aho

No subqueries!? Simple solution is the best solution, thank you.