Mysql Select Question

[50r]

hello guys this seems simple but not in my case as per now.

 

i have a table of categories with

 

cat_id -------------------cat_parent_id----------- cat_name

7 ------------------------- 0 ---------------------- cat1

9 --------------------------- 0----------------------- cat2

11 -----------------------------7------------------------ cat3

 

 

now am using these categories as a menu on the front page. i have made a query that calls only cats that have a 0 for parent thats fine.

in this case

 

cat1 and cat2 are the main menu.

 

i want to have an icon on a category that has a child which is cat3 that will show a user that if they point a cursor there, they will see hiden childrens.

 

the proble is am on a home page that has no query in the url that i can use as a reference in my sql query.

 

what kind of query can i do to put an icon on cats that have a child? before even a use clicks on it.

Edited December 11, 2012 by 50r

[Barand]

You need to join the table to itself with a left join

 

SELECT a.cat_id, a.cat_parent_id, a.cat_name, b.cat_id
FROM category a
LEFT JOIN category b ON a.cat_id = b.cat_parent_id
WHERE a.cat_parent_id = 0

 

b.cat_id will be NULL where there is no child